SOLVED

New Contributor

# COUNTIFS function with multiple criteria

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

# Re: COUNTIFS function with multiple criteria

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

# Re: COUNTIFS function with multiple criteria

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

You can try these formulas.

# Re: COUNTIFS function with multiple criteria

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))

# Re: COUNTIFS function with multiple criteria

``=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.

# Re: COUNTIFS function with multiple criteria

Thank you for the tip. It works perfectly now.