Single count for repeated values with multiple criteria

Here's my problem. Let me explain with an example.

there are 2 columns on which I want to run a count in the foll manner :

Col 1 Col 2
A1 B1
A1 B2
A1 B2
A1 B3
A1 B3

Now When I Pivot this data what I see is :
A1 ----5
B1 -- 1
B2 -- 2
B3 -- 2
It counts the no of rows for each unique combination. But What I really want is to count each combination as 1 , regardless of how many times it repeats. Basically I want the row count for A1 as 3 and on my report I want to show that A1 went through 3 B values. So it aggregates as :
A1 = 3
At present it is aggregating as A1 = 5 based on the pivot above. Please tell me how I can achieve this?

Regards,
Shweta

Something like:

=COUNT(1/FREQUENCY(IF($A$1:$A$100="A1",IF($B$1:$B$100<>"",MATCH($B$1:$B$100,$B$1:$B$100,0))),ROW($B$1:$B$100)-ROW($B$1)+1))

where A1:B100 is the entire range... you can replace "A1" with an cell reference so you can copy down.

You must confirm the above formula with CTRL+SHIFT+ENTER not just ENTER and copy down.

Or with just ENTER,

=SUMPRODUCT((A$1:A$100="A1")*(B$1:B$100<>"")*(MATCH(A$1:A$100&"^"&B$1:B$100,A$1:A$100&"^"&B$1:B$100,0)=(ROW(A$1:A$100)-ROW(A$1)+1)))

Unfortunately both the options did not work. Please see the example below that I am working with. For example for Incident 640; I need to get a value of 3 that shows the Incident passed through 3 assigned groups. I need to get a count of unique combinations for each incident across the list of Incidents. Please suggest.

Incident ID ---------Assigned Group ---------------Count Formula ----SumProduct Formula
INC000000066636 Operations Command Center -----0 0
INC000000066637 Operations Command Center------0 0
INC000000066639 Operations Command Center 0 0
INC000000066639 Operations Command Center 0 0
INC000000066640 Infrastructure--------- ---------------0 0
INC000000066640 Operations Command Center 0 0
INC000000066640 Operations Command Center 0 0
INC000000066640 Production Control 0 0
INC000000066640 Production Control 0 0

Assuming data is in A2:B100, try these:

My formula:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and copied down

Haseeb's formula:

Please Login or Register  to view this content.

confirmed with ENTER and copied down.