In the example shown, the formula in H5 is: 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. 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: Because we are giving SUMIF more than one criteria, SUMIF will return more than one result. In the example shown, the result of SUMIF is the following array: {1,0.5,0,0,0} Note that we correctly get 1 for each “F” and 0.5 for each “H”., and blank values in the week generate zero. Finally, we use SUMPRODUCT to add up the values in the array returned by SUMIF. Because there is only a single array, SUMPRODUCT simply returns the sum of all values.

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.