Forum Discussion
Excel - distinct count
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!
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
- Thank you! This is great. The 348 is in Pivot 1 - column A, if you count the opp IDS it is 348.