PDA

View Full Version : [SOLVED:] Count unique values based on multiple criteria


RKramkowski
02-21-2012, 01:24 PM
Hi,

I've searched numerous forum and have not yet found an answer to my problem. Using a formula, I'd like to count unique occurrences in column A where Column B = a particular string AND Column C = a different string (and eventually Column D = yet another value).

I've uploaded a spreadsheet that I expanded on from another forum where the solution was the closest I could find. Cells F15, G15, and H15 are my attempt at making this work. After spending several days on this, I feel I'm no closer to a solution than when I started.

I found how to count occurrences based on the value of colum b and c but not UNIQUE values.

Any ideas?
Thanks,
Bob
CatDaddy
02-21-2012, 01:33 PM
sumproduct! check out the sticky on using the formula
Bob Phillips
02-21-2012, 03:20 PM
TRy this array formula

=SUM(IF(FREQUENCY(IF(($A$1:$A$15<>"")*($B$1:$B$15="Active")*($C$1:$C$15="a"),
MATCH($A$1:$A$15,$A$1:$A$15,0)),ROW(INDIRECT("1:"&ROWS($A$1:$A$15))))>0,1))

commit with Ctrl-Shift-Enter
RKramkowski
02-22-2012, 07:22 AM
I now know more about sumproduct - which will be immensely helpful! And XLD - that formula works beautifully. And thanks to CatDaddy I now know how it works.
santosonit
11-04-2019, 01:22 PM
hi!

How about having a text partial match as a criteria?
I've tried ($A$1:$A$30="Venda&*") but no success!

thanks!



TRy this array formula

=SUM(IF(FREQUENCY(IF(($A$1:$A$15<>"")*($B$1:$B$15="Active")*($C$1:$C$15="a"),
MATCH($A$1:$A$15,$A$1:$A$15,0)),ROW(INDIRECT("1:"&ROWS($A$1:$A$15))))>0,1))

commit with Ctrl-Shift-Enter
Bob Phillips
11-07-2019, 09:07 AM
Try

(Left($A$1:$A$30,5)="Venda")
snb
11-07-2019, 09:17 AM
=SUMPRODUCT(--(LEFT($B$2:$B$15;5)&$C$2:$C$15="Vendaa");1/(COUNTIF($A$2:$A$15;$A$2:$A$15)))
santosonit
11-07-2019, 05:55 PM
Try

(Left($A$1:$A$30,5)="Venda")

I'll try the suggestion above! Thank you!
santosonit
11-07-2019, 05:57 PM
Ended using the one I began with and I'm afraid the file is getting too heavy - calculation/processing wise. These matrix formulas are killing it, but we seem to be bumping into one of excel's limitation.
Thanks a lot for looking into it.

Cheers,




=SUMPRODUCT(--(LEFT($B$2:$B$15;5)&$C$2:$C$15="Vendaa");1/(COUNTIF($A$2:$A$15;$A$2:$A$15)))