Return the sum of multiple columns with VLOOKUP

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. With VLOOKUP we pass within the third argument col_index_num the array of columns we'd like to sum: {2,3,5,13}, always enclosed in curly brackets. This will return an array of 4 values {98,52,75,60}. :


{=VLOOKUP(B7,A1:M4,{2,3,5,13},0)}

2. To sum the elements of the array, we use the function sum and then press CTRL + SHIFT + ENTER or we can use the function SUMPRODUCT that doesn't require CTRL + SHIFT + ENTER.

{=SUM(VLOOKUP(B7,A1:M4,{2,3,5,13},0))}
=SUMPRODUCT(VLOOKUP(B7,A1:M4,{2,3,5,13},0))

3. An different approach would be to use SUMPRODUCT with multiple OR criteria described in this post.

Related posts

• Return the sum of multiple columns with SUMPRODUCT