COUNTIFS with multiple criteria across non-contiguous ranges

Hi All,

I'm looking for a fomula to replace/streamline the below multiple instances of COUNTIFS adding together. Essentially I need a COUNTIFS with multiple criterai accross non-contiguous ranges.

=COUNTIFS(Model!$D$24:$D$9999,$E5,Model!$F$24:$F$9999,">="&J$4,Model!$F$24:$F$9999,"<"&EDATE(J$4,1))+
COUNTIFS(Model!$D$24:$D$9999,$E5,Model!$G$24:$G$9999,">="&J$4,Model!$G$24:$G$9999,"<"&EDATE(J$4,1))+
COUNTIFS(Model!$D$24:$D$9999,$E5,Model!$H$24:$H$9999,">="&J$4,Model!$H$24:$H$9999,"<"&EDATE(J$4,1))+
COUNTIFS(Model!$D$24:$D$9999,$E5,Model!$I$24:$I$9999,">="&J$4,Model!$I$24:$I$9999,"<"&EDATE(J$4,1))+
COUNTIFS(Model!$D$24:$D$9999,$E5,Model!$J$24:$J$9999,">="&J$4,Model!$J$24:$J$9999,"<"&EDATE(J$4,1))+
COUNTIFS(Model!$D$24:$D$9999,$E5,Model!$K$24:$K$9999,">="&J$4,Model!$K$24:$K$9999,"<"&EDATE(J$4,1))

Any help will be greatly appreciated

Well, you say non-contiguous ranges, but then in your sample the date ranges are all contiguous (unless you're referring to column D). So, if all the comparisons of dates are in contiguous cells like your sample, you could try this:

=SUM((Model!D24:D9999=E5)*(Model!F24:K9999>=J$4)*(Model!F24:K9999<EDATE(J$4,1)))

If the dates aren't contiguous, then this doesn't help much, but it's a little shorter than your COUNTIFS formula:

=IFERROR(ROWS(FILTER(Model!D24:D9999,(Model!D24:D9999=E5)*
((Model!F24:F9999>=J$4)*(Model!F24:F9999<EDATE(J$4,1))+
(Model!G24:G9999>=J$4)*(Model!G24:G9999<EDATE(J$4,1))+
(Model!H24:H9999>=J$4)*(Model!H24:H9999<EDATE(J$4,1))+
(Model!I24:I9999>=J$4)*(Model!I24:I9999<EDATE(J$4,1))+
(Model!J24:J9999>=J$4)*(Model!J24:J9999<EDATE(J$4,1))+
(Model!K24:K9999>=J$4)*(Model!K24:K9999<EDATE(J$4,1))))),0)

If these don't help you, please attach a sample file so we have something to test with.

Hi Gregb11,

You're right, in the sample formula I provided the ranges were not non-contiguous ranges, although your initial formula work perfectly for this example. And, your additional formula will help when I have the need to move to non-contiguous ranges.

Thank you very much for you help, it is greatly appreciated.

Thread marked as solved and rep added.

You're welcome, and thanks for the rep and feedback.