Count unique values based on multiple criteria

Printable View

02-21-2012, 01:24 PM
RKramkowski

1 Attachment(s)

Count unique values based on multiple criteria

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
02-21-2012, 01:33 PM
CatDaddy

sumproduct! check out the sticky on using the formula
02-21-2012, 03:20 PM
Bob Phillips

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
02-22-2012, 07:22 AM
RKramkowski

Thank you both

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.
11-04-2019, 01:22 PM
santosonit

hi!

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

thanks!

Quote:

Originally Posted by xld

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
11-07-2019, 09:07 AM
Bob Phillips

Try

(Left($A$1:$A$30,5)="Venda")
11-07-2019, 09:17 AM
snb

PHP Code:

=SUMPRODUCT(--(LEFT($B$2:$B$15;5)&$C$2:$C$15="Vendaa");1/(COUNTIF($A$2:$A$15;$A$2:$A$15)))
11-07-2019, 05:55 PM
santosonit

Quote:

Originally Posted by xld

Try

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

I'll try the suggestion above! Thank you!
11-07-2019, 05:57 PM
santosonit

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,

Quote:

Originally Posted by snb

PHP Code:

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