Formula challenge - 2D lookup and sum - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Friday, July 3, 2020

Formula challenge - 2D lookup and sum

Formula challenge - 2D lookup and sum

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. 
Lookup and sum cups after 12 PM on Tue and Thu

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

  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.
HideAnswer (click to expand)
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.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

No comments:

Post a Comment