Counting unique combinations in two columns

[jasondebolt]

Hi,

I am interested in finding a function that will count the number of
unique combinations a in two columns (columns A& B). For example:

Column A Column B Column C Column D
ABC 03/2007 =A1&B1 =SUMPRODUCT((1/
COUNTIF(C1:C5,C1:C5&"")))
BCA 03/2007 =A2&B2
CAB 07/2007 =A3&B3
ACB 07/2007 =A4&B4
ABC 03/2007 =A5&B5

In the case above, there are 4 unique combinations in columns A and B
(ABC 03/2007, BCA 03/2007, CAB 07/2007, ACB 07/2007). I don't count
the second instance of ABC 03/2007 becuase it is already in the first
row.

Is there a cleaner way of doing this? I've tried using sumproduct, but
I can't seem to get it to work the way I want it to. Does anyone know
how this can be done?

There are only four possible strings in column A (ABC, BCA, CAB, ACB)

Thanks!
Jason

 

[Mike H]

Providing you have no blanks in your concatenated range try:-

=SUM(1/COUNTIF(C1:C5,C1:C5))

If you have blanks (which will cause your formula below to fail) then try:-

=SUM(IF(FREQUENCY(IF(LEN(C1:C5)>0,MATCH(C1:C5,C1:C5,0),""),IF(LEN(C1:C5)>0,MATCH(C1:C5,C1:C5,0),""))>0,1))

Both are array formula ctrl+shift+enter

Mike

 

[JMB]

You could also try
=SUMPRODUCT(--(MATCH(A1:A5&B1:B5,A1:A5&B1:B5,0)=ROW(A1:A5)-ROW(A1)+1))

or, if there could be blank cells in column A or B and you want to exclude
those, you could try
=SUMPRODUCT((A1:A5<>"")*(B1:B5<>"")*(MATCH(A1:A5&B1:B5,A1:A5&B1:B5,0)=ROW(A1:A5)-ROW(A1)+1))

 

[jasondebolt]

You could also try
=SUMPRODUCT(--(MATCH(A1:A5&B1:B5,A1:A5&B1:B5,0)=ROW(A1:A5)-ROW(A1)+1))

or, if there could be blank cells in column A or B and you want to exclude
those, you could try:
=SUMPRODUCT((A1:A5<>"")*(B1:B5<>"")*(MATCH(A1:A5&B1:B5,A1:A5&B1:B5,0)=ROW(A1:A5)-ROW(A1)+1))

great, thanks!