RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 790
- Office Version
- 365
- Platform
- Windows
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:
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!
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:
Excel 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!