Counting Formula

Colin Legg · #6 06-20-2011, 01:51 AM

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:

Code:

=H3-I3

and fill down to K8.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Counting Colors	g48dd	Excel	2	03-13-2011 09:28 PM
Automatic counting in a looping powerpoint	RCU	PowerPoint	0	09-22-2010 12:28 PM
listing and counting words	gencoglux	Word	2	05-07-2010 05:10 PM
Counting Weeks	leroytrolley	Excel	1	08-18-2008 11:12 AM
Counting Legal Words	bulletrick	Word	0	12-30-2005 03:22 AM