Microsoft Office Forums Counting Formula
 Register FAQ Search Today's Posts Mark Forums Read

#1
06-18-2011, 05:23 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 88
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?

#2
06-19-2011, 03:19 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

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")`
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:
Code:
`=COUNTIF(B2:B100,"<>")-H2`
Adjust the ranges to suit (I have assumed that row 1 contains column headers).
#3
06-19-2011, 10:03 AM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 88

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
06-19-2011, 10:33 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

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
06-19-2011, 04:56 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 88

Thank you much Colin.

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)
#6
06-20-2011, 01:51 AM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

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"

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:
Code:
`=H3-I3`
and fill down to K8.
#7
06-20-2011, 07:19 PM
 Karen615 Windows 7 64bit Office 2010 64bit Advanced Beginner Join Date: Jun 2011 Location: Chicago Posts: 88

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post g48dd Excel 2 03-13-2011 09:28 PM RCU PowerPoint 0 09-22-2010 12:28 PM gencoglux Word 2 05-07-2010 05:10 PM leroytrolley Excel 1 08-18-2008 11:12 AM bulletrick Word 0 12-30-2005 03:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:04 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top