Counting Duplicate Values Only Once in a Column B by Matching Criteria in Column A

[TheRussian68]

Question should be straight forward. I searched and being a newbie didn't find a solution - at least one that I understood.

Assume the table below. I would like count the number of unique numbers from the each of the names (Cat, Dog, Mouse). The real world problem is I have about 800,000 call records where each row contains the name of the service customer subscribes to (represented by Cat, Dog, Mouse) and the actual subscription # associated with the outbound call. So I want to count the number of Unique Subscriptions that are found in the Subscription Column based on the unique Plan Name.

In the example below, I look at Column A, find each name (Say Cat) and then look at the corresponding value in Column B (Sub #) and count it exactly once. Cat has Sub # "1" found in row 2 and row 6 so I only count the occurrence of Sub # 1 exactly once. Cat has Sub # 2 occuring at row5 and Sub #3 at row 12. The value returned for unique subscriptions associated with all the Cat names is exactly 3 since we ignored the one occurrence of a duplicate (the Sub #1 ). Hopefully what I explained makes sense.

A B C D E 1 Name Sub # Name # Unique Sub 2 Cat 1 Cat 3 3 Dog 4 Dog 2 4 Dog 5 Mouse 3 5 Cat 2 6 Cat 1 7 Mouse 6 8 Dog 4 9 Mouse 7 10 Dog 5 11 Mouse 8 12 Cat 3 <tbody> </tbody>

<tbody>
</tbody>

 

[TheRussian68]

I can do it by adding a helper column where I count the number of occurrences of a particular value in the row in the array from the top to where that value is found in the column - e.g. =COUNTIF($E$2:E21,E21). Then I can do a COUNTIFS on two criteria but was looking for a more elegant solution.

 

[Marcelo Branco]

Try

Array formula in E2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$12=D2,MATCH(B$2:B$12,B$2:B$12,0)),ROW(B$2:B$12)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

If the column Sub# contains only numbers, not text, you can replace MATCH(B$2:B$12,B$2:B$12,0) by B$2:B$12

M.

 

[D_Nelson]

Try

Array formula in E2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$12=D2,MATCH(B$2:B$12,B$2:B$12,0)),ROW(B$2:B$12)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

If the column Sub# contains only numbers, not text, you can replace MATCH(B$2:B$12,B$2:B$12,0) by B$2:B$12

M.

This is perfect for what I am after. Just one question. What if column B was for dates. Is there a way of keeping it so that this formula where it matches A and counts single unique values after a particular date?