05-06-2020 12:25 AM
05-06-2020 12:25 AM
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!
05-06-2020 02:17 AM
@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.
05-07-2020 01:10 AM
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,
05-07-2020 01:16 AM
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?
05-07-2020 02:43 AMSolution
@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.