Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-18-2011, 05:23 PM
Karen615 Karen615 is offline Counting Formula Windows 7 64bit Counting Formula Office 2010 64bit
Competent Performer
Counting Formula
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default 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


Reply With Quote
  #2  
Old 06-19-2011, 03:19 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Counting Formula Windows 7 32bit Counting Formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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).
Reply With Quote
  #3  
Old 06-19-2011, 10:03 AM
Karen615 Karen615 is offline Counting Formula Windows 7 64bit Counting Formula Office 2010 64bit
Competent Performer
Counting Formula
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 06-19-2011, 10:33 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Counting Formula Windows 7 32bit Counting Formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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?
Reply With Quote
  #5  
Old 06-19-2011, 04:56 PM
Karen615 Karen615 is offline Counting Formula Windows 7 64bit Counting Formula Office 2010 64bit
Competent Performer
Counting Formula
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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
File Type: xls Tests.xls (197.0 KB, 13 views)
Reply With Quote
  #6  
Old 06-20-2011, 01:51 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Counting Formula Windows 7 32bit Counting Formula Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #7  
Old 06-20-2011, 07:19 PM
Karen615 Karen615 is offline Counting Formula Windows 7 64bit Counting Formula Office 2010 64bit
Competent Performer
Counting Formula
 
Join Date: Jun 2011
Location: Chicago
Posts: 112
Karen615 is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Formula 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
Counting Formula listing and counting words gencoglux Word 2 05-07-2010 05:10 PM
Counting Formula Counting Weeks leroytrolley Excel 1 08-18-2008 11:12 AM
Counting Legal Words bulletrick Word 0 12-30-2005 03:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:08 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft