Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values | Excel Worksheet Functions | |||
Count Unique Values but not Filtered or Hidden Values | Excel Worksheet Functions | |||
counting unique values conditionally | Excel Discussion (Misc queries) | |||
Count unique values in one column if values in corresponding columnare null? | Excel Programming | |||
Count unique values and create list based on these values | Excel Worksheet Functions |