## Summary

In this formula challenge, we have data showing cups of coffee sold at a small kiosk for a week. What formula will look up and sum the total cups sold after 12:00 PM on Tuesday and Thursday? Relevant cells are shaded in green.

### The problem

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. ### The challenge

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

### Constraints

1. Your formula should dynamically locate the cells to sum, without hardcoded references. In other words, =SUM(D10:D14,F10:F14) is not valid.
2. 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:

``````=SUMPRODUCT(data*(times>0.5)*((days="Tue")+(days="Thu")))
``````

Where the expression (times>0.5) is equivalent to:

``````=(times>TIME(12,0,0))
``````

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.