I have a column (A) that lists different schools.


Then in column B, I have a list of different students who attend these schools.
Then there are three columns for tests.
Column D
Column E
Column F
Each student has to have all 3 tests completed.
If the student completed the test, there's a "Yes" in the column. If they did not, there's a "No" in the column.
Some could have completed all three tests, some could have completed only one test and not the other two - for example.
I have to count how many students have taken all 3 tests.
Then in another column, I have to count how many students have not completed all three tests. (So, there could be one, two or three "No" in any of the columns. What's the best way to handle this?
Thank you in advance

Hi Karen,

To get the count of students who have completed all the tests:
Let's say you put that formula in cell H2. Then, to get the count of students who have not completed all three tests you could use this formula:
Adjust the ranges to suit (I have assumed that row 1 contains column headers).

Thank you for your help Colin!

The first formula works great! The second one produced zero.
I changed the cell references too.

Do you know what might be wrong?

Thanks again, Karen

Hi Karen,

That would imply that all of the students have completed all of the tests. If that's not the case then please can you attach the workbook for us to see?

Thank you much Colin.
Attached is the spreadsheet.

If if's not too much trouble, would you please explain the formula below in layman's terms?
=COUNTIFS(A3:A295,"<>",C3:C295,"Yes",D3295,"Yes",E 3:E295,"Yes")

Attached Files

Tests.xls (197.0 KB, 13 views)

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:
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:
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:
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:
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.

Thank you so much Colin! You have been such a big help. I really appreciate you taking the time to help me.