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