Return the Sum of Two or More Columns with VLOOKUP

If you have ever wished that the VLOOKUP function could return the sum of two or more related columns, this trick will get you there.

Objective

Before we get into the details, let’s be clear about our objective. We have some transactions that were exported from our accounting system as shown below.

Transactions by Jeff Lenning

We would like to write a formula that will look up any given TransID and return the sum of three columns, Amount, Shipping, and Tax. This is illustrated in the screenshot below, where we enter a TransID such as 10578 and Excel computes the Total (1,076.36 = 1,006 + 10 + 60.36).

VLOOKUP by Jeff Lenning

Since this is essentially a lookup task, our first instinct is to use VLOOKUP. However, we know that VLOOKUP can only return one related value, not the sum of multiple related values.

One common workaround is to add a helper column to the data that sums the three columns and then use a VLOOKUP to return the value from the new helper column. However, whenever possible in practice, we prefer to work with the data as it comes so that the workbook is easy to update in future periods.

Another common workaround is to write a formula that adds up three VLOOKUP functions, such as the following.

=VLOOKUP(...) + VLOOKUP(...) + VLOOKUP(...)

However, such a formula can be difficult to update and maintain over time since any change to the arguments need to be made three times instead of once. The good news is that we can wrap a SUMPRODUCT function around a single VLOOKUP function. Let’s get started.

SUMPRODUCT

When the SUMPRODUCT has a single argument, it behaves much like the SUM function because it simply returns the sum of the values. When the SUMPRODUCT has multiple arguments, it returns the sum of the product of its arguments. To accomplish our objective, we will use the SUMPRODUCT to return the sum of the values in a single argument.

Basically, we will wrap a SUMPRODUCT function around a VLOOKUP function that returns an array of values, specifically, the related values from multiple columns. The SUMPRODUCT will sum the values in the array returned by the VLOOKUP function. How do we convince the VLOOKUP function to return an array of values? That brings us to our next discussion.

Technical Note: we need to use SUMPRODUCT instead of SUM because the SUMPRODUCT function is designed to work with arrays and will thus return the sum of the array returned by the VLOOKUP function. The SUM function would return the sum of the first array element only. However, you could use the SUM function and then array-enter it with Control+Shift+Enter if preferred.

Array of Values

We can ask the VLOOKUP function to return an array of values by enclosing the third argument in curly braces {}. This causes the VLOOKUP function to actually return an array that contains multiple elements. For example, if we wanted the VLOOKUP function to return the values from the 3rd, 4th, and 5th columns to an array with three elements, we could use the following formula.

=VLOOKUP(C6,B12:F18,{3,4,5},0)

Where:

  • C6 is the lookup value
  • B12:F18 is the lookup range
  • {3,4,5} are the column numbers that have the values we want returned, enclosed in curly braces to create the array of values
  • 0 tells VLOOKUP to use exact match logic

This VLOOKUP function actually returns an array of values, and the array elements are 1006, 10, and 60.36. If you enter it into a single cell and press Enter, Excel will display only the first element. But, we don’t want to display the first element, we want to compute the sum of all array elements. So, let’s put it all together and bring it home.

Technical note: you can confirm the VLOOKUP returns an array of values by array-entering the formula into three cells across at once (Ctrl+Shift+Enter). Excel will display the three array elements in the three cells.

SUMPRODUCT and VLOOKUP

Now that we understand that using the curly braces causes VLOOKUP to return an array of values, let’s add up the values in the returned array with the SUMPRODUCT function.

Here is a screenshot of our workbook.

SUMPRODUCT by Jeff Lenning

The formula in C7 follows.

=SUMPRODUCT(VLOOKUP(C6,B12:F18,{3,4,5},0))

The SUMPRODUCT function returns the sum of the array elements returned by the VLOOKUP function. This is essentially the same as SUMPRODUCT({1006, 10, 60.36}), which returns 1076.36.

When we press Enter, we confirm that it works as expected…yes!

Results by Jeff Lenning

Note: there is no need to array-enter this formula by using Control-Shift-Enter.

And that my friend is one way to return the sum of two or more columns with VLOOKUP. If you have another approach, please share by posting a comment below…thanks!

Additional Resources

 

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

30 Comments

  1. joe szczepaniak on October 29, 2015 at 1:10 pm

    That’s really clever! How did you run across the option of putting an array of columns into the VLOOKUP function?

    • jefflenning on October 30, 2015 at 6:06 am

      Joe,
      That technique came from a participant in a discussion during one of my Excel sessions…pretty cool!
      There is so much to learn about Excel…I wish I had discovered that trick years ago 🙂
      Thanks
      Jeff

  2. Jon D Schaefer on May 19, 2016 at 8:21 am

    Excellent – thank you. Is there any way to replace the {3,4,5} part with a name? Like can I name a group of columns and reference them that way? Not finding anything about naming an array.

    • Kurt LeBlanc on July 27, 2016 at 8:55 am

      Hey Jon,

      You can create a named formula, as in ={3,4,5}, and use that instead:)

      Let me know if you need anymore help!
      Kurt LeBlanc

  3. Nick on May 31, 2016 at 4:05 pm

    Jeff, this is a really cool solution.
    More to the point – you explain it clearly and succinctly in a way that is not only easy to follow, but helps to ‘stick’ it in the brain.
    You’re clearly a gifted teacher.

    Nick

    • jefflenning on May 31, 2016 at 4:37 pm

      Thanks 🙂

  4. jain_tech on June 23, 2016 at 9:25 pm

    Will this similarly work with hlookup?

    • Kurt LeBlanc on June 30, 2016 at 1:57 pm

      Correct! It works because SUMIFS() works horizontally as well as vertically.

  5. Srikanth on August 11, 2016 at 9:21 pm

    Thanks for the useful information.. I would like to add multiple values which are in the same column (unlike the example here where the addition is along the same row)…How do I do that. Can you please suggest.?

    • Kurt LeBlanc on August 16, 2016 at 12:13 pm

      Hey Srinkanth!

      Absolutely, with HLOOKUP:) It is exactly the same logic fortunately, just with nth row as the third argument instead of column. If you transpose the table in the sample file and adjust the lookup formula to HLOOKUP(), you can see hat even the array works as expected:)

      Let me know if you need any help with the setup,
      Kurt LeBlanc

      • shaibajit on September 5, 2016 at 3:31 am

        Hi Kurt,
        My table area is A1:K30. My Lookup value matches with H1. And I want to add from H10 to H28. Instead of writing all the row numbers from 10 to 28 withing HLookup [ like – hlookup{10,11,12,13,…….,26,27,28} ] is there any other options?

        • Kurt LeBlanc on September 6, 2016 at 8:19 am

          I understand now:)

          Unfortunately, you have to enter each of the individual numbers in the array…or you can create a named formula that refers to the array, and use that in your lookup.

          Let me know if that is clear
          Kurt LeBlanc

  6. Adrian Ramirez on October 14, 2016 at 12:24 pm

    This post is to subscribe to follow-up emails (meant to do that earlier, but forgot)

    • jefflenning on October 14, 2016 at 12:32 pm

      Great…thanks!

  7. Sam on November 7, 2016 at 10:28 pm

    Thanks this is a great post. However, what’s missing is the solution that sums up multiple values in one column. All the values I want to sum up are not in multiple columns but in the same column. How do I make this work? Thanks in advance

  8. Janeske on June 19, 2017 at 2:23 am

    Good day,
    I really benefit from this – Thank you!!
    Just a question I have a vlookup table A:Q3229 and needs to sum the months from Jan to June (depending on the month). Please see my formula below:
    {=IFERROR(SUM(VLOOKUP(E18,budget!A:Q,{5,6,7,8,9,10},0)),””)} this formula is working perfectly – however is there a way if you change the month in the input sheet that it can add a column (Jan to July) to add instead of adding column 11 to my formula?
    I would like to sent the sheet out to individuals to fill in and they won’t be able to change the formula.

    • Jeff Lenning on June 19, 2017 at 4:18 am

      I’d probably suggest using SUMIFS instead of VLOOKUP, which supports the use of comparison operators (such as > and <) in the criteria arguments. I've written a bunch of posts on SUMIFS if you haven't used it before: https://www.excel-university.com/tag/sumifs/

      Hope it helps,
      Jeff

  9. Luis Alberto Siles Tapia on June 30, 2017 at 8:00 pm

    Hello,
    If I where to have multiple “lookup values” in a table and I´d like the sum of all ID “a”
    ID
    a 3 4 1
    b 4 5 2
    c 6 1 5
    a 7 2 0
    d 1 1 9
    a 0 1 7

    ID a
    Total = 3+4+1+7+2+0+0+1+7
    How could I achive this? using the formula it only gives me: 3+4+1

  10. Mantu Singh on May 25, 2018 at 1:16 pm

    Very useful post for Excel users. Thanks a lot for this post.

  11. Tuan Nguyen on August 21, 2018 at 9:39 am

    Hello Jeff,

    Thank you for the incredibly useful article!

    I have a unique situation and I was wondering if you could help me out. I have monthly income statement data (from Jan-Dec) in one tab and an output page on another tab. On the output page, I have two cells – beginning month and ending month; which reflects the performance period I want to look at. I.e. if I wanted to see the performance for January thru June, I would enter 1 and 6 in the respective cells.

    My question is: is there any way I can sum the revenues (as an example) for the different columns by using a combination of vlookup and sumproduct? I can’t use the technique you described because the column numbers here are hard-coded whereas I am trying to make my output page more dynamic so I don’t have to go in and change the formula every time.

    Thank you so much for your help in advance.

  12. FM on January 28, 2020 at 11:06 am

    Many thanks, but could I simply things;
    =SUMPRODUCT(($G$5:$I$11)*($L$4=$E$5:$E$11))) = 1076.36

    simply using sumproduct without a lookup function will work as above where cell L4 = 10578
    There are many ways, just thought I would add another!
    Kind regards

    • Jeff Lenning on January 28, 2020 at 11:23 am

      Nice … thanks!

  13. theo tran on March 3, 2020 at 11:54 am

    very good !.

  14. Matt on February 9, 2021 at 5:16 am

    Hi Jeff,
    Do you know of a way this could be expanded so that the formula is able to find more than one “hit” for the lookup value?
    I have a table of data where the lookup value could appear up to three times in column B. The formula would then have to be able to sum values from, for example, cell D12, E12, F13 &/or F14.
    I think it would be either Sumproduct or Index Match Match but I have tried numerous different formulas with no luck.
    Hopefully this makes sense and thank you in advance for any help you can offer.
    Kind regards
    Matt

  15. yrla on December 6, 2021 at 10:14 pm

    do you have formula for the vlookup with match function and sum multiple columns of match function?

  16. Joe on August 6, 2022 at 10:37 am

    I have a multi sheet spread sheet keeping track of job hours. I have used VLOOKUP in succession to sum all the hours on multiple sheets and it works great… Until it gets to a sheet that does not contain the lookup value. I have searched all over for my issue, and VLOOKUP may be the incorrect solution. I was wondering if I could rattle anyone’s brain to make this work.

    I have 1 excel document with 52 tabs. Each tab is a work week starting from January so WW1 is all the hours for sed jobs the first week of January. “joes house 2 hours ; mikes house 3 hours”… I then have created all 52weeks as tabs… WW2, WW3 etc… Until WW52.

    This is the function I made to add hours together…

    =SUM(VLOOKUP(O30,’WW29′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW30′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW31′!$A$7:$M$110,{13},FALSE))

    And it works great. But when that job is finished it is not on (for example WW32 tab), I get the #N/A error. So for example, as the previous one works great when I expand the formula to cover the next sheet (“WW32” EXAMPLE OF NEXT PAGE WIOTHOUT LOOKUP VALUE)

    =SUM(VLOOKUP(O30,’WW29′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW30′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW31′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW32′!$A$7:$M$110,{13},FALSE))

    I get the #N/A error because the job is not listed on WW32. But I may need to add hours to a certain job on WW45 due to say a change order. Therefore I would like to sum all 52 weeks and have a total sheet at the end. Is there a way to make VLOOKUP skip a sheet that does not have the referenced value and continue summing it till the end? I apologize, this may be as clear as mud but I will clarify anything if need be. I have also tried IFERROR. You can set IFERROR to return text or even blanks, but does not seem to cover summing. I’m looking for how to SUM multiple sheets when some of the sheets do not contain the lookup value. When using IFERROR function, instead of RETURNING #N/A it just returns “YOU’VE ENTERERED TOO MANY ARGUMENTS FOR THIS FUNCTION”…

    =IFERROR(VLOOKUP(O30,’WW29′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW30′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW31′!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,’WW32′!$A$7:$M$110,{13},FALSE),””)And that’s just 3 sheets. 

    Any help would be greatly appreciated.

    P.S. I have tried with CTRL+SHIFT+ENTER as well to no avail.

  17. MANAS RANJAN RANASINGH on November 3, 2022 at 2:51 am

    HOW TO CONVERT THE COLUMN APPROACH IN TO DYNAMIC

  18. Freda on May 2, 2023 at 3:54 pm

    I am trying to use the IF(ISNA(sumproduct(VLOOKUP($A2,’Apr 2023 DX’!$A:$J,{4,5,6,7,8,9,10},0)))) but it doesn’t want to work. Any suggestions. I am trying to have it return an empty cell if “N/A” is the answer.

Leave a Comment