Count unique values based on multiple criteria

[RKramkowski]

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]

sumproduct! check out the sticky on using the formula

[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

[RKramkowski]

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]

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]

Try

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

[snb]

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

[santosonit]

Try

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

I'll try the suggestion above! Thank you!

[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,

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