Frequency formula counting distinct text values with multiple criteria

mssbass

Active Member
Joined
Nov 14, 2002
Messages
253
Platform
  1. Windows
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))
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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))
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top