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