Where codes is the named range J4:J5 and values is the named range K4:K5.
Sometimes you may want to sum multiple values retrieved by a lookup operation. In this example, we want to sum holiday time taken each week based on a code system, where F = a full day, and H = a half day. If a day is blank, no time was taken.
The challenge is to find a formula that both looks up and sums the values associated with F and H.
How this formula works
The core of this formula is SUMIF, which is used to lookup the correct values for F and H. Using SUMIF to lookup values is a more advanced technique that works well when the values are numeric, and there are no duplicates in the "lookup table".
The trick in this case is that the criteria for SUMIF is not a single value, but rather an array of values in the range C5:G5:
SUMIF is a function to sum cells that meet a single criteria. SUMIF can be used to sum cells based on dates, numbers, and text that match specific criteria. SUMIF supports logical operators (>,,=) and wildcards (*,?) for partial matching....
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.