Counting unique products from three columns

[RockandGrohl]

Slight repost because it was phrased poorly with bad examples, so hopefully this is clearer.

On the attached image I have Tour Dates in B, Campaign Name in C and Product Name in D.

What I am looking to do is find out for each Campaign Name, how many unique products exist. A unique product is one where the combination of Tour Date and Product Name is distinct.

Looking at the example image, there are 8 lines for Campaign "35. Irish Setter". However, there are two duplicate lines for Bournemouth Weekend - there are two products on the same date for the 6th and 13th November.

This means there are actually 6 unique products on the tour. The green highlighted column shows the expected result. I can get this result with the following formula:

=SUMPRODUCT((1/COUNTIFS($B$2:$B$9,$B$2:$B$9,$C$2:$C$9,$C$2:$C$9,$D$2:$D$9,$D$2:$D$9)))

However, this only works when the range exactly matches the number of lines for the corresponding campaign

What I would like to do is have this formula go down to the bottom of the range (Row 850) and have it count the products based on the campaign name in column C on the current row.


This should be possible I think I'm just missing the syntax to do so. Thank you!

 

[Fluff]

Duplicate Quickfire Formula - Count unique products
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).