#1




Counting Formula
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 
#2




Hi Karen,
To get the count of students who have completed all the tests: Code:
=COUNTIFS(B2:B100,"<>",D2:D100,"Yes",E2:E100,"Yes",F2:F100,"Yes") Code:
=COUNTIF(B2:B100,"<>")H2 
#3




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 
#4




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? 
#5




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") 
#6




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") 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... 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") 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,"<>") 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: Code:
=H3I3 
#7




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

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Counting Colors  g48dd  Excel  2  03132011 09:28 PM 
Automatic counting in a looping powerpoint  RCU  PowerPoint  0  09222010 12:28 PM 
listing and counting words  gencoglux  Word  2  05072010 05:10 PM 
Counting Weeks  leroytrolley  Excel  1  08182008 11:12 AM 
Counting Legal Words  bulletrick  Word  0  12302005 03:22 AM 