Frequency formula counting distinct text values with multiple criteria

[mssbass]

I've set up a formula that is correctly counting my data using 1 criteria; however, I'm ready to add a second and third criteria to my formula. How do I nest in my 2nd & 3rd if statement? The additional criteria I need to add is Data!B2:B10000=K2 and Data!A2:A10000=L5.

=SUMPRODUCT(--FREQUENCY(IF(Data!D2:D10000=K1,MATCH(Data!C2:C10000,0)),ROW(Data!C2:C10000)-ROW(Data!C2)+1)>0))

 

[mrhstn]

Hi

Combined them in the if using multiplication

=SUM(--(FREQUENCY(IF((($B$2:$B$10000=K2)*($A$2:$A$10000=L5)),MATCH($C$2:$C$10000,$C$2:$C$10000,0)),ROW($C$2:$C$10000)-ROW($C$2)+1)>0))

This need confirmed with ctrl+shift+enter which will result in the formula being wrapped in {}

HTH

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Data!C2:C10000=""),IF(Data!A2:A10000=L5,IF(Data!B2:B10000=K2,IF(Data!D2:D10000=K1,MATCH(Data!C2:C10000,Data!C2:C10000,0))))),ROW(Data!C2:C10000)-ROW(Data!C2)+1),1))

 

[mssbass]

Thank you all - looks like the following 'nested' If statements worked for me:

=SUM(--(FREQUENCY(IF(Data!D2:D10000=S2,IF(Data!B2:B10000=S3,IF(Data!A2:A10000=S6,MATCH(Data!C2:C10000,Data!C2:C10000,0)))),ROW(Data!C2:C10000)-ROW(Data!C2)+1)>0))

and of course ctrl+shift+enter for the formula to work

 

[Aladin Akyurek]

Thank you all - looks like the following 'nested' If statements worked for me:

=SUM(--(FREQUENCY(IF(Data!D2:D10000=S2,IF(Data!B2:B10000=S3,IF(Data!A2:A10000=S6,MATCH(Data!C2:C10000,Data!C2:C10000,0)))),ROW(Data!C2:C10000)-ROW(Data!C2)+1)>0))

and of course ctrl+shift+enter for the formula to work

What has been proposed would also work... Just try.