Forum Discussion

Nadia0705's avatar
Nadia0705
Copper Contributor
Apr 20, 2023

COUNTIFS help for and or criteria

Please help - I keep getting error/spill or n/a error messages.

  • I have columns E7:G128 on SLD page that may contain the word Below
  • In column D7:D128 on SLD page I have the class numbers 3-18
  • Trying to do a formula on a different page that will count how many 'Below' are within E7:G128 BUT ONLY if they are in class 3 OR 4 (column D)

Counting the number of 'below's works a treat using =SUM(COUNTIF(SLD!E7:G128,"*Below*"))

but when I try to add in the class 3 or 4 criteria it doesnt work =SUM(COUNTIFS(SLD!E7:G128,"*Below*",SLD!D7:D128{"*3*","*4*"}))

 

I have tried the above with and without arrays, * , referencing 3 & 4 in new cells, nothing seems to work.

 

Many thanks in advance

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 20, 2023

    Nadia0705 

    The ranges in COUNTIFS must have the same shape and size.

    Try

    =SUMPRODUCT((ISNUMBER(SEARCH("Below",SLD!E7:G128))*ISNUMBER(SEARCH("3",SLD!D7:D128))))+SUMPRODUCT((ISNUMBER(SEARCH("Below",SLD!E7:G128))*ISNUMBER(SEARCH("4",SLD!D7:D128))))

  • Nadia0705's avatar
    Nadia0705
    Copper Contributor
    Apr 20, 2023
    in addition to above - I can't just refence E7:G25 (rows that are classes 3 and 4) due to other using changing the sort order of other columns and therefore rows 7:25 may no longer be 3 and 4 data - if that makes sense
    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      Apr 20, 2023
      The formula I provided in my previous response should still work even if the sort order of the columns changes. It counts the number of cells in columns E7:G128 that contain “Below” and where the corresponding cell in column D is either 3 or 4, regardless of the sort order.
      • Nadia0705's avatar
        Nadia0705
        Copper Contributor
        Apr 20, 2023
        That's brilliant all working- Thank you!
        In anticipation of my next question, what if I had to reference not only E7:G128 on SLD but additionally EYFS!E7:G128 - so two different pages containing Below - would it be like this?

        =SUMPRODUCT((SLD!E7:G128="Below")*(((SLD!D7:D128=3)+(SLD!D7:D128=4))>0))+((EYFS!E7:G128="Below")*(((EYFS!D7:D128=3)+(SLD!D7:D128=4))>0)

        I also will need to do some that have up to 5 classes for each formula, so
        ((SLD!E7:G128="Below")*(((SLD!D7:D128=3)+(SLD!D7:D128=4)+(SLD!D7:D128=5)+(SLD!D7:D128=6))>0)
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 20, 2023

    Nadia0705 

    It looks like you’re trying to count the number of cells in columns E7:G128 on the SLD page that contain the word “Below” and are in either class 3 or 4 (as indicated in column D7:D128 on the SLD page).

    One way to do this is by using a combination of the SUMPRODUCT and -- (double unary) functions:

    =SUMPRODUCT((SLD!E7:G128="Below")*(((SLD!D7:D128=3)+(SLD!D7:D128=4))>0))

    This formula counts the number of cells in columns E7:G128 that contain “Below” and where the corresponding cell in column D is either 3 or 4.

     

    I hope this helps!

Share

Resources