Question: In Microsoft Excel 2003/XP/2000/97, I have a list of time values in column B. I need to count the values that are before 8:00am, the values that fall between 8:00am and 8:19am, and the values that are after 10:00am.
How can I do this?
Answer: This can be done in Excel with an array formula.
Let's look at an example.
In cell D2, we've created the following array formula:
=SUM((HOUR(B2:B10)<8)*1)
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:B10)<8)*1)}
This formula would return the number of time values that are before 8:00am.
In cell E2, we've created the following array formula:
=SUM((HOUR(B2:B10)=8)*(MINUTE(B2:B10)>=0)*(MINUTE(B2:B10)<=19))
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:B10)=8)*(MINUTE(B2:B10)>=0)*(MINUTE(B2:B10)<=19))}
This formula would return the number of time values that fall between 8:00am and 8:19am.
In cell K2, we've created the following array formula:
=SUM((HOUR(B2:B10)>=10)*1)
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:B10)>=10)*1)}
This formula would return the number of time values that are after 10:00am (including 10:00am exactly).
