Conditionally count unique values
 

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

Conditionally count unique values
 

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

--
Jacob (MVP - Excel)


"CLR" wrote:

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


.

Conditionally count unique values
 

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

"Jacob Skaria" wrote in message
...
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))

--
Jacob (MVP - Excel)


"CLR" wrote:

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


.