Friday, August 5, 2016

MS Excel 2003: Use an array formula to count the number of children who will attend lunch

Question: I’m trying to create a formula in Microsoft Excel 2003/XP/2000/97. I have a situation where the user in a daycare center needs to count the number of children that will be in attendance at lunch time. Here is what the spreadsheet looks like:
Column AColumn BColumn C
Name:Monday InMonday Out
Joe8:00 AM1:00 PM
Mary9:30 AM12:00 PM
Cindy7:00 AM11:30 PM
Sam8:00 AM5:00 PM
Isaac12:00 PM6:00 PM
Daylon7:30 AM12:00 PM
Robert9:00 AM6:00 PM
Alliah11:00 AM4:00 PM
The formula needs to count only those kids where the value in column B is less than 12:00PM and the value in column C is greater than 12:00PM. Lunch is served at 12 noon so only 5 of the 8 listed above would remain to have lunch.
How can I do this?
Answer: This can be done in Excel with an array formula.
Let's look at an example.
Microsoft Excel
In cell A11, we've created the following array formula:
=SUM(((HOUR(B2:B9)*60+MINUTE(B2:B9))<720)*((HOUR(C2:C9)*60+MINUTE(C2:C9))>720))
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM(((HOUR(B2:B9)*60+MINUTE(B2:B9))<720)*((HOUR(C2:C9)*60+MINUTE(C2:C9))>720))}
What this formula does is convert the time values in column B and column C into minutes, then count only those kids where the time value in column B is less than 720 minutes (ie: 12:00PM) and the time value in column C is greater than 720 minutes (ie: 12:00PM).
To explain further, 12:00PM = (12 hours * 60 minutes) which equals 720 minutes.

No comments:

Post a Comment