Coupler.io Blog

Excel VLOOKUP SUM: Formula Examples

  • Fitrianingrum Seto

    • Calculating the sum of values across multiple columns is a common problem in Excel. The SUM function will work in most cases. However, what if you have many rows and need to quickly find a particular row to sum up related values in different fields? In that case, the combination of SUM and VLOOKUP is a great time saver!

    This blog post focuses on several examples of using Excel VLOOKUP and SUM in one formula. It also covers how to write these functions in VBA code.

    How to use SUM and VLOOKUP together in Excel

    In most situations, the combination of SUM and VLOOKUP functions in Excel is useful when calculating the total of matching values in multiple columns. For example, to find the total purchase of a specific customer across 12 months, as the following screenshot shows:

    You may be wondering if you could simply use the SUM function. Why not just use =SUM(B6:M6)? Well, of course you can! But what if you want to determine how much someone spends by changing the value in A1? In this case, you would need to use a combination of VLOOKUP and SUM instead.   

    This is the common combination formula for SUM + VLOOKUP:

    =SUM(VLOOKUP(lookup_value, table_array, col_index_num, [match_type])

    The useful part of this combination is when you use an array of numbers in the third parameter of VLOOKUP, for example:

    =SUM(VLOOKUP(lookup_value, table_array, {2,3,4}, [match_type])

    Why? Because you won’t really need the SUM function if your VLOOKUP only returns one column — unless you use multiple VLOOKUPs inside a SUM.

    If you’re looking for how to use VLOOKUP and SUMIF together, check out our article on that: Excel SUMIF with VLOOKUP Formula Examples.

    Examples of Excel SUM and VLOOKUP to sum all matches values in multiple columns

    Let’s explore some examples of using VLOOKUP and SUM functions together in one formula in Excel. 

    #1: Excel VLOOKUP and SUM multiple columns 

    In the example below, we use Excel VLOOKUP and SUM to calculate the total sales of “Barberton Daisy” for 6 months, from January to June.

    Here is the formula in J2, as you can see in the screenshot above:

    =SUM(VLOOKUP(I2,A2:G9,{2,3,4,5,6,7},FALSE))

    Explanation: 

    The VLOOKUP formula looks up for “Barberton Daisy” (the value in I2) in the first column of range A2:G9. Then, it returns values from the 2nd, 3rd, … , 7th column of the range (column B-G). After the VLOOKUP function has been executed, it returns an array of values with these elements: {2000,5000,4000,4000,5000,5000}. You can then use this result in a SUM calculation. The formula will look like this below, which equals 25000.

    =SUM({2000,5000,4000,4000,5000,5000})

    #2: Excel VLOOKUP and SUM: Use data from another sheet

    What if you want to do the calculation from another sheet? Let’s say you have two worksheets, Summary and Sheet1, and in the Summary sheet you want the total sum of per-product sales for data on Sheet1. 

    In this case, you can use the following combination of VLOOKUP and SUM in B2, then apply it down to B9. 

    =SUM(VLOOKUP(A2,Sheet1!$A$2:$G$9,{2,3,4,5,6,7},FALSE))

    Explanation: 

    As you can see, to access the lookup table, the formula uses Sheet1!$A$2:$G$9 instead of simply A2:G9. This is because the lookup table is located in Sheet1, which is on a different sheet from where we wrote the formula. You need to apply the formula to B3-B9 so by using an absolute reference to the lookup table, you can ensure that its range doesn’t change in these cells. 

    #3: Excel VLOOKUP and SUM matches values across multiple sheets

    Now, suppose you have the following spreadsheet containing sales data. It has three sheets: Summary, Sheet1, and Sheet2. You want to calculate the total sales in Sheet1 and Sheet2 for each product. Then, put the result in the Summary sheet.

    In this case, you can simply use two VLOOKUP functions inside a SUM. Here’s the formula in B2 — and you need to copy it down to B9:

    =SUM(VLOOKUP(A2,Sheet1!$A$2:$G$9,{2,3,4,5,6,7},FALSE),VLOOKUP(A2,Sheet2!$A$2:$G$9,{2,3,4,5,6,7},FALSE))

    Explanation:

    The first VLOOKUP gets monthly sales in Sheet1 across January-June. The second VLOOKUP gets the monthly sales in Sheet2 across July-December. Finally, the SUM function adds the results from both VLOOKUPs.

    #4: Excel VLOOKUP and SUM with array formula

    Suppose you have the following spreadsheet that contains two tables. The first table lists product names (flowers) and their price per stem. The second table contains customer names, product names, and total stems purchased. Now, you want to calculate how much the total purchase is for a given customer in J2.

    If you use the latest Microsoft 365 upgrade, you can use the combination of SUM and VLOOKUP, as shown in the below screenshot. Note: it’s an array formula, however, pressing Ctrl+Shift+Enter to wrap the formula in curly braces is no longer necessary in the current version of Excel.

    =SUM(VLOOKUP(E2:E6,A2:B9,2,FALSE)*F2:F6*(D2:D6=J2))

    Explanation: 

    Note: You can’t use the above solution if you use an older version of Excel, for example, Excel 2010. As an alternative, you can use the SUM and LOOKUP functions instead. Keep in mind that the lookup table in the range A2:B9 needs to be sorted alphabetically if you are using the LOOKUP function. Additionally, you’ll have to press Ctrl+Shift+Enter to make the array formula work. Here’s how the final formula should look if you use this method: 

    Examples of what VLOOKUP and SUM can’t do: Sum all matches values in multiple rows 

    #5: Excel VLOOKUP and SUM: Lookup value in duplicate rows

    With the following data, suppose you want to calculate how many sales were generated by Orchid for six months, from January to June. 

    See that you have duplicate rows for Orchid. In this case, you can’t use the combination of Excel SUM and VLOOKUP. Using VLOOKUP to search for “Orchid” always returns the first matching value only. 

    One alternative is by using the SUMPRODUCT function, as follows:

    =SUMPRODUCT((A2:A9=J2)*C2:H9)

    Explanation:

    The first expression A2:A9=J2 checks the range A2:A9 and returns an array of TRUE FALSE. If any cell equals “Orchid“, it will return TRUE, otherwise it returns FALSE: 

    {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}

    The result above is then multiplied by the values in range C2:H9 and creates the following array:

    {0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;3000,4000,5000,3000,4000,2000;2000,4000,1000,4000,3000,1000;0,0,0,0,0,0;0,0,0,0,0,0}

    Finally, the SUMPRODUCT calculates the total of all the elements in the above array and returns 36000.

    #6: Excel VLOOKUP and SUM values between two dates

    In the example below, suppose you want to sum up the sales of multiple records by order date. There are two columns and a few rows that you need to sum. Notice that there are even rows with the same order dates.

    A combination of SUM and VLOOKUP won’t be able to solve this problem. One alternative is to use the SUM function with two nested XLOOKUP functions, as shown in the following formula:

    =SUM(XLOOKUP(F2,A2:A16,C2:D16):XLOOKUP(G2,A2:A16,C2:D16))

    Please note that the XLOOKUP function only works in Microsoft 365 and Excel for the web (Excel Online version). Also, see our guide on XLOOKUP: Excel XLOOKUP function.

    Explanation: 

    The formula uses two nested XLOOKUP functions to create a range dynamically by returning the starting cell and ending cell on either side of the colon range operator. The first XLOOKUP returns the first cell reference in the range, while the second XLOOKUP returns the last cell reference in the range. After the nested functions have been executed, the SUM function would look like this:

    =SUM(C7:D11)

    Tip: You can use the Evaluate Formula dialog box to observe how the XLOOKUP functions are being evaluated and their return result. To do this, first, select the cell that we want to evaluate, in this case H2. Then, in the Formula tab, click on the Evaluate Formula button in the Formula Auditing group. A dialog box will appear, allowing you to evaluate the formula, expression by expression.

    Excel VBA: SUM + VLOOKUP

    You may want to use SUM and VLOOKUP in VBA, for example, when you need to do it as a part of a larger task. Let’s say, every week you need to pull sales data from several external systems into Excel, do a bit of cleanup, and send a report in PDF related to a certain product. Additionally, in a specific part of that report, you need to do a calculation using SUM and VLOOKUP. 

    Tip: You can pull data from external sources into Excel without coding by using an integration tool such as Coupler.io. With it, you can save your time by importing data from different sources like Airtable, Jira, Shopify, HubSpot, and many others into Excel. The best part? You can even set automatic data refresh on the schedule you want!

    Check out the complete list of Coupler.io integration with Excel. 

    How to write a formula of VLOOKUP + SUM in Excel VBA

    Both SUM and VLOOKUP are worksheet functions in Excel. You can use them in VBA by typing WorksheetFunction.<function_name>. Here are the syntaxes of both functions: 

    WorksheetFunction.Sum(Number1,Number2,...,Number30)

    Where Number1,Number2,...,Number30 are the values that you want to sum. You can have up to 30 arguments.

    WorksheetFunction.Vlookup(lookup_value, table_array, col_index_number, [match_type])

    The syntax is similar compared to when you’re using it in a cell, and the number of arguments is also the same. However, in VBA, when you pass an array in the third argument, the VLOOKUP will return the result for the first element only. Thus, you need to do the VLOOKUP in a loop if you want to return multiple results.

    How to sum up numbers in multiple columns using VLOOKUP + SUM in Excel VBA

    Now, let’s look at the example below. Suppose you have the following table and want to sum the total sales generated by “Sunny Smile” using VBA.

    To do that, follow the steps below:

    Step 1: Press Alt+F11 to open the Visual Basic Editor (VBE).

    Step 2: Create a new Sub, for example, “MySumVlookup“.

    Sub MySumVlookup()
    ProductName = "Sunny Smile"
    ColIndexNumbers = Array(2, 3, 4, 5, 6, 7)
           
    For Each Index In ColIndexNumbers
      Result = WorksheetFunction.Sum(Result, _
        WorksheetFunction.VLookup(ProductName, Range("A2:G10"), Index, False))
    Next
    
    MsgBox (Result)
End Sub

    Screenshot:

    Explanation: 

    We declared a variable called ColIndexNumbers that contains the indexes of columns in range A2:G10 that we want to sum. The code loops through each element in the array and in each loop it performs VLOOKUP and adds the result into the Result variable. Finally, the code returns the end result in a message box.

    Step 3: Run the Sub by pressing the green triangle button in the toolbar.

    You will see a message box showing the result, as follows: 

    When your Excel VLOOKUP + SUM is not working

    So, you’re using a combination of SUM and VLOOKUP functions in Excel but running into a problem? Check for the following things:

    Wrapping up

    We’ve covered various examples of using Excel VLOOKUP and SUM together. You’ve seen the different scenarios where this combination can be helpful, as well as some limitations it has with summing data in multiple rows. Check out also how you can nest IF VLOOKUP in Excel.

    In addition, if you’ve been struggling with how to pull data from external systems, such as Jira, Shopify, WordPress, etc., or even JSON formatted data from REST APIs into Excel, try Coupler.io. Take advantage of this integration tool to do the imports with ease. You don’t even need to write any VBA code. ?

    Exit mobile version