Need help counting distinct values

[James_Raju]

Hi!

I have a data set containing a list of people that participate in our meetings and I need help calculating the number of meetings organized in each location (?).

We will continue to organize meetings and details of the same will be added to the second sheet (All Meetings) and I need the data to update on the first sheet (Sheet 1).

Thanks in advance!

James Raju

[#2]

@James_Raju Here you go. Please see attached.

https://excelsirji.com/excel-function-countif/

[#3]

@jukapil 

Hi!

Thanks for the help!

The data set contains details of 52 meetings in total (example: Meeting No. 1 in S.no 2 to 232 is the same meeting). I need help counting the number of meetings held in each location.

[#4]

@James_Raju If you are open to a solution using Power Query (PQ) and Pivot Tables, please see attached. No need for COUNTIF, SUMIF etc. And you have the possibility to count distinct occurrences of meetings. Must say that I'm still a beginner in the area of PQ (coming from an Excel for Mac background), so a real expert in this area can probably make it nicer.

[#5]

@James_Raju see attached. I listed all the meetings. Now you can see the count for each meeting. It counted the staff number and meeting

[#6]

@jukapilHi !

I'm really sorry buddy! It's still not what I was looking for. I would like to know how many meetings were held in each location. For example: 1 Press Meeting and 5 Staff meetings were organized in location 1. I arrived at this by filtering the data and excluding duplicates, but I had to do that manually. I need another, hopefully 'automatic' method of getting this information.

Appreciate the help,

James Raju

[#7]

@Riny_van_EekelenHi!

This is exactly what I wanted! I'm fairly new to excel and haven't gotten around to using Pivot tables and such. Is there a way for the Pivot table to update itself every time I update or add more information to the set? I've tried adding more data but the values on the table remain the same. Is there any way I can get it to include this new data into the results without having to make a new table each time?

Many thanks!

James Raju

[#8 · Accepted solution]

@James_Raju The file I sent you uses Pivot Tables based on a your data that gets "manipulated" in Power Query. I created a dynamic named range of your data table. When you add data and refresh the query, everything should update instantly. On the Data ribbon you should find an icon "Refresh All". Try and see what happens. Would advise you, though, to learn about Power Query.

If you are on a modern version of Excel that supports the functions UNIQUE and FILTER, you might want to use them in stead in your existing table that already works for the attendee count. I've attached a revised file for your reference.