SOLVED

COUNTIFS function with multiple criteria

New Contributor

I need help with embedding the COUNTIF function along with the COUNTIFS function.  I have two different criteria that I need to count and add together in the first range and then apply a third criteria from another range to it.  I keep getting the error that I do not have enough criteria to use this function.

 

=COUNTIFS(COUNTIF(range1, "criteria1")+COUNTIF(range1, "criteria2")), range2, "criteria3")

5 Replies
I forgot to mention this is for text criteria.
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Chad_Kinser1973 

=SUM(COUNTIFS(D4:D23,{"A";"B"},E4:E23,"option4"))
=SUMPRODUCT(((D4:D23="A")+(D4:D23="B"))*(E4:E23="option4"))

You can try these formulas.

countifs.JPG

This worked perfectly, Thank you. My next problem is getting =SUM(COUNTIFS(D4:D23,{"A";"B"} to work with a date range I manually enter. I am trying to find all records with the previous criteria that are due within the next 30 days.
=SUM(COUNTIFS(range1,{"A";"B"},range2, ">="&Sheet1!G2, range2, "<=" &Sheet1!G3))

@Chad_Kinser1973 

=SUM(COUNTIFS(C4:C23,{"A";"B"},D4:D23, ">="&G2, D4:D23, "<=" &G3))

Your formula works perfectly in my sheet. In cells G2 and G3 i'd enter the formulas

=TODAY()

=TODAY()+30

to automatically update the range of dates.

countifs date range.JPG 

Thank you for the tip. It works perfectly now.