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...
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)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!
