Counting unique products from three columns

Status
Not open for further replies.

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
494
Office Version
  1. 2010
Platform
  1. 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:

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!
 

Attachments

  • help2.PNG
    help2.PNG
    133.9 KB · Views: 5

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
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).
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,119,009
Messages
5,575,532
Members
412,673
Latest member
KD23
Top