Forum Discussion
Excel - distinct count
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.
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!
- so AE24 in 'Pivot' of 221 is filtered by School Term Semester. Without that filter it shows 628 which I'm guessing is because there are 138 non-unique values.
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!!!
JennyHoA20181 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