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'...
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.
- Ahh this is great thank you! What is the difference between cell C10 in 'sheet 1' value = 490 and cell AE24 in 'Pivot' value = 221 please?
What I would need to show in table is for example, a total value of 348 for Spain and Portugal? i.e count of all opportunity ids in Spain and Portugal - see Pivot 1. To see this 348 by Campaign type I would need to count the opportunity ID once per campaign type. i.e if Harry appeared 10 times in campaign type = organic, i only want to that he appeared once. Hope I make sense!
