Excel - distinct count

[JennySommet]

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

[#2]

@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"))

[#3]

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!

[#4]

@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.

[#5]

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!

[#6]

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.

[#7]

@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!!!

[#8]

@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

[#9]

Thank you! This is great. The 348 is in Pivot 1 - column A, if you count the opp IDS it is 348.

[#10]

@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.

[#11]

Hi sorry for not explaining clearly! It is so hard to put into words :) I want to know what % of my pipeline were 'engaged with'. So out of the 348 for the Spain & Portugal Pipeline, what % attended each of the campaign types. I know each one attended multiple ones, but did they attend at least one?

[#12]

ok you sort of lost me there. you want a % for each campaign type? So the latest file I sent you can divide the number for an individual campaign by the first column which is the total for any campaign. BUT that is a % of those that went to something, went to that campaign vs not going to THAT campaign type, it is not a list of % values for each campaign relative to EACH OTHER (i.e. those % will not add up to 100%). As with all statistics, you need to be careful defining exactly what you want and understand the values you are calculating. For example if you look at that % for Organic it is close to 50% of that 348, but that doesn't mean organic is responsible for 50% of all unique IDs encounters but I think it would be correct to say almost 50% of all unique IDs had an organic campaign event.
In any case, I think you have everything you need, now you just need to wield that power carefully ;)