Forum Discussion
COUNTIFS help for and or criteria
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)
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!
=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.
=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.
