COUNTIFS help for and or criteria

[Nadia0705]

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

[#2]

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

[#3]

@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!

[#4]

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.

[#5]

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

[#6]

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)

[#7]

@Nadia0705 

=SUM(MMULT(ISNUMBER(SEARCH("below",SLD!E7:G128))*(ISNUMBER(SEARCH(3,SLD!D7:D128))+ISNUMBER(SEARCH(4,SLD!D7:D128))),ROW(1:3)^0))

An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. 

[#8]

@Nadia0705 

=SUM(MMULT(ISNUMBER(SEARCH("below",SLD!E7:G128))*(ISNUMBER(SEARCH(3,SLD!D7:D128))+ISNUMBER(SEARCH(4,SLD!D7:D128))+ISNUMBER(SEARCH(5,SLD!D7:D128)))+ISNUMBER(SEARCH("below",EYFS!E7:G128))*(ISNUMBER(SEARCH(3,EYFS!D7:D128))+ISNUMBER(SEARCH(4,EYFS!D7:D128))+ISNUMBER(SEARCH(5,EYFS!D7:D128))),ROW(1:3)^0))

You can try this formula across two sheets. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. 

[#9]

@Nadia0705 

You can reference multiple sheets in a single formula.

The formula is correct for summing the values in both the SLD and EYFS sheets where the value in column D is either 3 or 4 and the value in columns E to G is “Below”.

For your second question, if you want to include additional classes in your formula, you can add more conditions to the part of the formula that checks the value in column D.

For example, if you want to include classes 3 to 6, you can use the formula you provided:

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

This will sum the values where the value in column D is either 3, 4, 5 or 6 and the value in columns E to G is “Below”.

I hope this helps!