# Counting unique products from three columns

Status
Not open for further replies.

#### RockandGrohl

##### Active Member
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
133.9 KB · Views: 5

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

Replies
2
Views
59
Replies
12
Views
182
Replies
1
Views
51
Replies
0
Views
268
Replies
1
Views
121