Forum Discussion
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 Tryi...
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)
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)
=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.