Conditionally count unique values

  • Thread starter Thread starter CLR
  • Start date Start date
C

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
 
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))
 
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
 
Back
Top