Conditionally count unique values

[CLR]

Hi All.......
I have 23 rows in column AU that contains the string "Receipt Traveler Put
Away"
And, for those 23 rows, I have 22 unique TEXT strings in column K and one
duplicate.

I'm trying to count the unique TEXT strings in column K that have "Receipt
Traveler Put Away" in column AU.....answer would be 22

Using the following formula, I get 17.45111111......anybody see what's
wrong?.......or another way?

=SUMPRODUCT((K2:K1595<>"")/COUNTIF(K2:K1595,K2:K1595&"")*(AU2:AU1595="Receipt
Traveler Put Away"))

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Jacob Skaria]

Try the below. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

=SUM(--(FREQUENCY(IF((AU2:AU1595="Receipt Traveler Put Away"),
MATCH(K2:K1595,K2:K1595,0)),ROW(INDIRECT("1:"&ROWS(K2:K1595))))>0))

 

[CLR]

Thanks Jacob..........your Array formula serves the purpose I was looking
for.........(now that I learned how to code in an Array Formula
<g>)..........still wondering about that SUMPRODUCT version tho

Vaya con Dios,
Chuck, CABGx3