Return the sum of multiple columns with SUMPRODUCT

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. We use the function SUMPRODUCT with multiple AND (asterisk: *) and OR (plus: +) criteria.

=SUMPRODUCT((B2:M4)*(A2:A4=B7)*
      ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11)))

First we pass the range we want to sum (B2:M4), and then add the conditions: 1) Year 1 (A2:A4=B7); and 2) Months to sum: ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11))). In English, the year must be equal to Year 1 and the months must be January or February or April or December.

Related posts

• Return the sum of multiple columns with VLOOKUP