Forum Discussion
Excel - distinct count
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.
JennyHoA20181 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.