The data below shows cups of coffee sold at a small kiosk for a week at different hours of the day. The times in column B are valid Excel times.
What formula in cell I5 will correctly sum the total cups sold after 12:00 PM on Tuesday and Thursday? Relevant cells are shaded in green.
For your convenience, the following named ranges are available:
data = C5:G14
times = B5:B14
days = C4:G4
Download the Excel workbook, and leave your answer as a comment below.
- Your formula should dynamically locate the cells to sum, without hardcoded references. In other words, =SUM(D10:D14,F10:F14) is not valid.
- Use named ranges when possible to make your formula easy to read.
Many great answers! The most common approach was to use the SUMPRODUCT function like this:
Where the expression (times>0.5) is equivalent to:
This works because Excel handles times as fractional values of 1 day, where 6:00 AM is 0.25, 12:00 PM is 0.5, 6:00 PM is 0.75, etc.
If SUMPRODUCT used this way is new to you, this formula is based on the same idea, and includes a full explanation. SUMPRODUCT may seem intimidating, but I encourage you to give it a try. It is an amazing tool.