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>
[TD="class: xl63"][TABLE="class: grid, width: 10"]
<tbody>[TR]

</tbody>

[/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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?