Hi Karen,
Sure. COUNTIFS is a worksheet function which was introduced in Excel 2007. It returns a count where multiple conditions are met. It takes the form:
Code:
COUNTIFS( Range1, Condition1, Range2, Condition2, Range3, Condition3, Range4, Condition4, etc...)
Code:
=COUNTIFS(B2:B100,"<>",D2:D100,"Yes" ,E2:E100, "Yes" ,F2:F100,"Yes")
This formula says:
Give me the count where all of the following conditions are met on each row:
B2:B100 does not equal "" (ie, something has been put in the cell)
D2:D100 equals "Yes"
E2:E100 equals "Yes"
F2:F100 equals "Yes"
In your spreadsheet, you adapted the formula I suggested like this:
Code:
I3
=COUNTIFS(A3:A295,"<>",C3:C295,"Yes",D3:D295,"Yes",E3:E295,"Yes")
J3
=COUNTIFS(A296:A528,"<>",C296:C528,"Yes",D296:D528,"Yes",E296:E528,"Yes")
etc...
You're using different
range references in each formula because you are separating out by schools: School 7 is in rows 3:295, school 8 is in rows 8:528 etc..
This isn't a good way of doing it: it's time consuming to get the formulas set up and it's hard to maintain them. It would be better to have a generic formula which can be used in all cases. You can do this by adding another
condition to the formula. You can put this formula in I3 and then copy it down to I8:
Code:
COUNTIFS($A$3:$A$1560,$G3,$B$3:$B$1560,"<>",$C$3:$C$1560,"Yes",$D$3:$D$1560,"Yes",$E$3:$E$1560,"Yes")
G3 holds the value 7 (representing school 7) so this new formula says:
Give me the count where all of the following conditions are met on each row:
A3:A1560 equals 7
B3:B1560 does not equal "" (ie, something has been put in the cell)
D3:D1560 equals "Yes"
E3:E1560 equals "Yes"
F3:F1560 equals "Yes"
In column H you can apply the same principal. Replace the current formula in H3 with:
Code:
=COUNTIFS($A$3:$A$1560,$G3,$B$3:$B$1560,"<>")
And fill down to H8.
Finally, since you have the total students tested and fully tested per school, to get the number of students who have not completed all 3 tests you can put this formula in K3:
and fill down to K8.