SUMPRODUCT to calculate unique occurences of string in column of d

[WildWill]

I have:
A1 = "A"
A2 = "A"
A3 = "B"
A4= "C"
A5= "A"

and

B1= "Apples"
B2= "Pears"
B3= "Strawberries"
B4= "Pears"
B5= "Apples"

I want to calculate the following:

For all the occurences of "A" in Column A, I want to count the uniquely
different TYPES of values that are contained in column B.

E.g. in the example above, the answer will be "2", because there are 3 x
occurences of "A" in column A, and these 3 occurences related to 2 occurences
of "Apples" and 1 of "Pears", thus 2 unique strings of data.

Please help.

 

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF(A1:A5="A",IF(B1:B5<>"",MATCH("~"&B1:B5,B1:B5&"",0)))
,ROW(B1:B5)-ROW(B1)+1),1))

Hope this helps!

http://www.xl-central.com

 

[Elkar]

Try this:

=ROUND(SUMPRODUCT(--(A1:A5="A"),1/COUNTIF(B1:B5,B1:B5&"")),0)

HTH
Elkar

 

[Domenic]

This formula may not return the desired result in some circumstances.
For example, if A1:B5 contains the following data...

A Apples
A Pears
B Pears
C Pears
A Apples

....the formula will return 1, instead of 2, which is the desired result.

Hope this helps!

http://www.xl-central.com