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.
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.
View the discussion thread.
Quick, clean, and to the point.