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 Download the Excel workbook, and leave your answer as a comment below.
Constraints
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. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
