Forum Discussion
Excel - distinct count
Hello,
I hope someone can help. I need to do a distinct count in excel of the 'Opportunity ID' (column G in 'Campaign Data' tab.
The outcome would need to look like Pivot 2 in the 'Pivot tab' - but not necessarily in a pivot as i'll create a table.
The question is, how many students (opportunity ids) attended one of the campaign types at least once. For example, 111 opportunity IDS are counted for Vertical Area = Spain & Portugal and Campaign Type = Open Day. But 5 of the 111 could be the same person it's just they went to 5 open days. I want to know if they went to at least 1.
In Pivot 1, if I count column A that gives me the correct number. It's just when I aggregate I get the duplicates.
Hope this makes sense!
Thanks
Jenny
11 Replies
JennyHoA20181 If you are using Excel 365 you will have the UNIQUE function. If you also need to FILTER that is also available. For example the following will filter by any Vertical Region starting with "Spain" and show only the Unique results:
=UNIQUE(FILTER('Campaign Data'!G:G,LEFT('Campaign Data'!M:M,5)="Spain"))- Thank you! How would I then get a nice table displayed of Vertical Area as rows, Campaign Type at the top and counting the unique opportunity IDS as values? May also need to filter by other values (i'e column J and M). Thanks!
JennyHoA20181 I would recommend you use the built in PivotTable functionality but maybe there is some reason you need to not use it (but I suspect we could solve that issue).
In any case, I did much the same as I showed above using UNIQUE to get the header row and the left column and then COUNTA(UNIQUE(FILTER())) to count how many match the header and the left column.
see attached. I didn't take the time to remove the "0" or the headers themselves.
