Unique Values Based on Text

[Ona]

Formula:
=SUMPRODUCT((RIGHT(B1:B10,5)="(a4) ")*((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&"")))

Please Login or Register  to view this content.

I want to get the number of unique values in column A, based on the RIGHT text of column B. In this particular example it should be 4, however that formula is returning 4.5. I believe this is because "c" is in column A twice, but should only be counted once for RIGHT, 5 = "(a4) ". Any help is appreciated!

[NBVC]

Try:

=COUNT(1/FREQUENCY(IF(RIGHT(B1:B10,5)="(a4) ",IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))),ROW(A1:A10)-ROW(A1)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER, although I am not sure why you are using Right(B1:B10,5)... is there other text in those B cells before the (A4)? If not, then maybe trim will work to get rid of trailing space...

e.g.

=COUNT(1/FREQUENCY(IF(TRIM(B1:B10)="(a4)",IF(A1:A10<>"",MATCH(A1:A10,A1:A10,0))),ROW(A1:A10)-ROW(A1)+1))

[DonkeyOte]

FWIW, the SUMPRODUCT equivalent would be:

Please Login or Register  to view this content.

though I make 5 unique values for "(a4) " in the sample data set (a,b,c,d,e)
(the 4.5 results from c being apportioned by 2 given 2 instances thereof)

[Ona]

FWIW, the SUMPRODUCT equivalent would be:

Please Login or Register  to view this content.

though I make 5 unique values for "(a4) " in the sample data set (a,b,c,d,e)
(the 4.5 results from c being apportioned by 2 given 2 instances thereof)

You are correct, it should have been 5 unique values with "(a4) ". I appreciate both of you for helping me with this, thank you very much! :D