Apr 20 2023 03:30 AM
Please help - I keep getting error/spill or n/a error messages.
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
Apr 20 2023 03:37 AM
Apr 20 2023 03:40 AM
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!
Apr 20 2023 03:42 AM
Apr 20 2023 03:46 AM
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))))
Apr 20 2023 03:54 AM
Apr 20 2023 04:12 AM
=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.
Apr 20 2023 04:18 AM
=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.
Apr 20 2023 04:53 AM
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!