May 24 2021 04:12 AM
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
May 24 2021 06:51 AM
@JennySommet 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"))
May 24 2021 09:16 AM
May 24 2021 09:49 AM
@JennySommet 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.
May 24 2021 10:30 AM
May 24 2021 08:19 PM
May 25 2021 12:19 AM
@mtarler How do I reflect the Count opp IDS 348 in Pivot, to show in your table on 'sheet 1' by campaign type please? To be clear, the total of row 10 on 'sheet 1', needs to be 348. I tried adding in the School Term Semester to your calculation, it didn't work. Thanks - almost there!!!
May 25 2021 06:10 AM
@JennySommet I updated the formula to eliminate the "nulls" as counting as 1 (i.e. many of those 1 values should have been 0s). I also applied your added criteria to the whole table. but other than that I don't see any other errors. Are you sure that row sum is 348? Is there another filter/criteria that 348 is based on? Another possibility is that some of the opp IDS values that are supposed to be the same have some slight difference? I tried adding TRIM and CLEAN to see if there were extra spaces or invisible characters added in some cases (this was not exhaustive), but that made no difference. Please confirm where that 348 comes from
May 25 2021 07:00 AM
May 25 2021 07:51 AM
@JennySommet Ah HA!! So you need to decide what you really want/need. Yes the number of unique IDs in Pivot1 is 348 but many of those unique IDs have counts in multiple campaign types. So if you highlight that pivot table (without the totals col or row) and look at the bottom bar that shows counts and totals and look at "Numerical Count", which is the count of cells that have numbers in them, you will see 612 and that exactly matches the 612 from the sum of the corresponding row in Sheet 1.
That said, I modified the formula in the 1st column of Sheet 1 under campaign_type to exclude the campaign type filter (i.e. now that 1st column is for all campaign types) and now you will see that 348 in that first column.
May 25 2021 07:57 AM
May 25 2021 08:32 AM