How to use VLOOKUP with SUM or SUMIF functions in Excel

In this tutorial, you will find a handful of advanced formula examples that demonstrate how to use Excel's VLOOKUP and SUM or SUMIF functions to look up and sum values based on one or several criteria.

Are you trying to create a summary file in Excel that will identify all instances of one particular value, and then sum other values that are associated with those instances? Or, do you need to find all values in an array that meet the condition you specify and then sum the related values from another worksheet? Or maybe you are faced with a more concrete challenge, like looking through a table of your company invoices, identifying all invoices of a particular vendor, and then summing all the invoice values?

The tasks may vary, but the essence is the same - you want to look up and sum values with one or several criteria in Excel. What kind of values? Any numeric values. What sort of criteria? Any : ) Starting from a number or reference to a cell containing the right value, and ending with logical operators and results returned by Excel formulas.

So, does Microsoft Excel have any functionality that can help with the above tasks? Of course, it does! You can work out a solution by combining Excel's VLOOKUP or LOOKUP with SUM or SUMIF functions. The formula examples that follow below will help you understand how these Excel functions work and how to apply them to real data.

Please note, these are advanced examples that imply you are familiar with the general principles and syntax of the VLOOKUP function. If not, the first part of our VLOOKUP tutorial for beginners is certainly worth your attention - Excel VLOOKUP syntax and general usages.

Excel VLOOKUP and SUM - find the sum of matching values

If you work with numerical data in Excel, quite often you have not just to extract associated values from another table but also sum numbers in several columns or rows. To do this, you can use a combination of the SUM and VLOOKUP functions as demonstrated below.

Source data:

Suppose, you have a product list with sales figures for several months, a column per each month. The source data is on the sheet named Monthly Sales:
Source data to look up and sum matching values

Now, you want to make a summary table with the total sales for each product.

The solution is to use an array in the 3rd parameter (col_index_num) of the Excel VLOOKUP function. Here is a generic formula:

SUM(VLOOKUP(lookup value, lookup range, {2,3,...,n}, FALSE))

As you see, we use an array constant in the third argument to perform several lookups within the same VLOOKUP formula in order to get the sum of values in columns 2,3 and 4.

And now, let's adjust this combination of VLOOKUP and SUM functions for our data to find the total of sales in columns B - M in the above table:

=SUM(VLOOKUP(B2, 'Monthly sales'! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))

Important! Since you are building an array formula, be sure to hit Ctrl + Shift + Enter instead of a simple Enter keystroke when you finished typing. When you do this, Microsoft Excel encloses your formula in curly braces like this:

{=SUM(VLOOKUP(B2, 'Monthly sales'!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}

If you press the Enter key as usual, only the first value in the array will get processed, which will produce incorrect results.
The SUM and VLOOKUP formula returns the sum of values in columns B - M in 'Monthly sales' sheet.

Tip. You may be curious why the formula displays [@Product] as the lookup value in the screenshot above. This is because I converted my data to table (Insert tab > Table). I find it very convenient to work with fully-functional Excel tables and their structured references. For example, when you type a formula into one cell, Excel automatically copies it across the entire column and in this way saves you a few precious seconds :)

As you see, using the VLOOKUP and SUM functions in Excel is easy. However, this is not the ideal solution, especially if you are working with big tables. The point is that using array formulas may adversely affect the workbook's performance since each value in the array makes a separate call of the VLOOKUP function. So, the more values you have in the array and the more array formulas you have in your workbook, the slower Excel works.

You can bypass this problem by using a combination of the INDEX and MATCH functions instead of SUM and VLOOKUP, and I will show you a few formula examples in the next article.

Download this VLOOKUP and SUM sample

How to perform other calculations with Excel VLOOKUP function

A moment ago we discussed an example of how you can extract values from several columns in the lookup table and calculate the sum of those values. In the same fashion, you can perform other mathematical calculations with the results returned by the VLOOKUP function. Here are a few formula examples:

Operation Formula example Description
Calculate average {=AVERAGE(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table' and calculates the average of values in columns B,C and D in the same row.
Find maximum value {=MAX(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table' and finds the max value in columns B,C and D in the same row.
Find minimum value {=MIN(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table' and finds the min value in columns B,C and D in the same row.
Calculate % of sum {=0.3*SUM(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table', sums values in columns B,C and D in the same row, and then calculates 30% of the sum.

Note. Since all of the above formulas are array formulas, remember to press Ctrl+Shift+Enter to enter them correctly in a cell.

If we add the above formulas to the 'Summary Sales' table from the previous example, the result will look similar to this:
Use VLOOKUP with other Excel functions to sum all matching values in an array and find the average, min or max value.

Download this VLOOKUP calculations sample

LOOKUP AND SUM - look up in array and sum matching values

In case your lookup parameter is an array rather than a single value, the VLOOKUP function is of no avail because it cannot look up in data arrays. In this case, you can use Excel's LOOKUP function that is analogues to VLOOKUP but works with arrays as well as with individual values.

Let's consider the following example, so that you can better understand what I'm talking about. Suppose, you have a table that lists customer names, purchased products and quantity (Main table). You also have a second table containing the product prices (Lookup table). Your task is to make a formula that finds the total of all orders made by a given customer.
Need a formula to find the total value of all products purchased by a given customer.

As you remember, you cannot utilize the Excel VLOOKUP function since you have multiple instances of the lookup value (array of data). Instead, you use a combination of SUM and LOOKUP functions like this:

=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))

Since this is an array formula, remember to press Ctrl + Shift + Enter to complete it.
A combination of SUM and LOOKUP functions that finds the total value of all products purchased by a given customer

And now, let's analyses the formula's ingredients so that you understand how each of the functions works and can to tweak it for your own data.

We'll put aside the SUM function for a while, because its purpose is obvious, and focus on the 3 components that are multiplied:

  1. LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)

    This LOOKUP function looks up the goods listed in column C in the main table, and returns the corresponding price from column B in the lookup table.

  2. $D$2:$D$10

    This component returns quantity of each product purchased by each customer, which is listed in column D in the main table. Multiplied by the price, which is returned by the LOOKUP function above, it gives you the cost of each purchased product.

  3. $B$2:$B$10=$G$1

    This formula compares the customers' names in column B with the name in cell G1. If a match is found, it returns "1", otherwise "0". You use it simply to "cut off" customers' names other than the name in cell G1, since all of us know that any number multiplied by zero is zero.

Because our formula is an array formula it iterates the process described above for each value in the lookup array. And finally, the SUM function sums the products of all multiplications. Nothing difficult at all, it is?

Note. For the LOOKUP formula to work correctly you need to sort the lookup column in your Lookup table in ascending order (from A to Z). If sorting is not acceptable on your data, check out an awesome SUM / TRANSPOSE formula suggested by Leo.

Download this LOOKUP and SUM sample

VLOOKUP and SUMIF - look up & sum values with criteria

Excel's SUMIF function is similar to SUM we've just discussed in the way that it also sums values. The difference is that the SUMIF function sums only those values that meet the criteria you specify. For example, the simplest SUMIF formula =SUMIF(A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10.

This is very easy, right? And now let's consider a bit more complex scenario. Suppose you have a table that lists the sales persons' names and ID numbers (Lookup_table). You have another table that contains the same IDs and associated sales figures (Main_table). Your task is to find the total of sales made by a given person by their ID. At that, there are 2 complicating factors:

  • The mail table contains multiple entries for the same ID in a random order.
  • You cannot add the "Sales person names" column to the main table.

Look up and sum the values that meet your criteria

And now, let's make a formula that, firstly, finds all sales made by a given person, and secondly, sums the found values.

Before we start on the formula, let me remind you the syntax of the SUMIF function:

SUMIF(range, criteria, [sum_range])
  • range - this parameter is self-explanatory, simply a range of cells that you want to evaluate by the specified criteria.
  • criteria - the condition that tells the formula what values to sum. It can be supplied in the form of a number, cell reference, expression, or another Excel function.
  • sum_range - this parameter is optional, but very important to us. It defines the range where the corresponding cells' values shall be added. If omitted, Excel sums the values of cells that are specified in the range argument (1st parameter).

Keeping the above info in mind, let's define the 3 parameters for our SUMIF function. As you remember, we want to sum all the sales made by a given person whose name is entered in cell F2 in the main table (please see the image above).

  1. Range - since we are searching by sales person ID, the range parameter for our SUMIF function is column B in the main table. So, you can enter the range B:B, or if you convert you data to a table, you can use the column's name instead: Main_table[ID]
  2. Criteria - because we have sales persons' names in another table (lookup table), we have to use the VLOOKUP formula to find the ID corresponding to a given person. The person's name is written in cell F2 in the main table, so we look it up using this formula: VLOOKUP($F$2,Lookup_table,2,FALSE)

    Of course, you could enter the name in the lookup criteria of your VLOOKUP function, but using an absolute cell reference is a better approach because this creates a universal formula that works for any name input in a given cell.

  3. Sum range - this is the easiest part. Since our sales numbers are in column C named "Sales", we simply put Main_table[Sales].

    Now, all you need is to assemble the formula's parts and your SUMIF + VLOOKUP formula is ready:

    =SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])

    SUMIF + VLOOKUP formula that looks up and sums values that meet the criteria you specify

Download this VLOOKUP and SUMIF sample

Formula-free way to do vlookup in Excel

Finally, let me introduce you to the tool that can look up, match and merge your tables without any functions or formulas. The Merge Tables tool included with our Ultimate Suite for Excel was designed and develop as a time-saving and easy-to-use alternative to Excel's VLOOKUP and LOOKUP functions, and it can be very helpful both to beginners and advanced users.

Instead of figuring out formulas, you simply specify your main and lookup tables, define a common column or columns, and tell the wizard what data you want to fetch.
Merge Tables Wizard - a formula-free way to do vlookup in Excel

Then you allow the wizard a few seconds to look up, match and deliver you the results. If you think this add-in may prove helpful in your work, you are most welcome to download a trial version by using the below link.

Available downloads

VLOOKUP with SUM and SUMIF - formula examples (.xlsx file)
Ultimate Suite - trial version (.exe file)

Table of contents

429 comments

  1. I am trying to perform a vlookup of order numbers where I have a couple of duplicates which I want to sum into one number in my final table. I have tried playing around with
    =IF(COUNTIF($P4:$V2598,$E4)>1, "Multiple results" ,VLOOKUP($E4,$P$4:$V$2598,6,FALSE))
    But all I can do is print a message rather than displaying the result of my addition. Any ideas?

  2. Hi everyone how can i consolidate the multiple duplicates with different values I need to sort out with the following ex below: I need the output that sums the total male > total female and total unkown under sample 1

    list gender value
    sample1 male 2
    sample1 female 3
    sample1 unknown 4
    sample1 male 2
    sample1 female 3
    sample1 unknown 4
    sample2 male 5
    sample2 female 6
    sample2 unknown 7

    • Glenn:
      Where your sample data is organized like this:
      Sample is in A30:A42, Gender is in B30:B42 and Value is in C30:C42 enter this formula in an empty cell:
      =SUMIFS(C31:C42,A31:A42,"=Sample1",B31:B42,"=Male")

  3. Hello,

    I am currently trying to Sum number on a sheet (SHEET 1) within my workbook. On the same row of the amount that I want to sum is a specific description to that amount.

    I am trying to SUMIF this amount to a different sheet (SHEET 2) corresponding to the matching description on the row from the other sheet (SHEET 1).

    The amount varies by each entry. The matching descriptions will also vary.

    I am trying to do this by keeping my filters and avoiding making tables for my data

  4. I have Dates as column headers in a sheet. In another sheet I have to input date and I want a formula to search the relevant column on the basis of date input and sum the entries using a criteria on another colum simultaneously.

  5. Hi,

    Please, I will so much appreciate if you could help explain the use of the "Info!" excel function works in the below formula:

    =VLOOKUP(P6,Info!C:I,7,0)

    Many thanks.

    • It's not an Excel function, it's the sheet where the data is located.

  6. Hi Sir/Mdm, I'm using =SUM(VLOOKUP(B2,A6:U105,MATCH(D2,A4:U4,0),0))"

    to find a certain cell value. However, I need to sum up the values of the cells from B2 value till B3 Value. How should I do it?

    Eg.

    B2 = 1950
    B3 = 1975
    D2 = Metal

    I need to enter value into Cell B2, B3 and D2. With the details entered, I need to sum up the value from "value of Row B2" to "value of Row B3" for the column of "value of Column D2".

  7. Dear Sir,
    Please give me suggestion/help for stock inventory sheet
    Name of contractor Apple Banana Etc
    Ram 2 5 4
    Shyam 4 3 5
    Hari 3 2 1
    Ram 5 6 6
    Hari 2 3 5
    Saral 4 4 3
    Ram 2 5 4
    Shyam 4 3 5
    Hari 3 2 1
    Ram 5 6 6
    Hari 2 3 5
    Saral 4 4 3
    I want to Total Issue to
    "Ram"
    Apple
    Banana
    Etc
    Which formula's tell me sir.

  8. Hi

    I am trying to add total quantity sold by Name and ID same person.

  9. =SUMIF('Q-1'!$B$4:$B$137,B4,'Q-1'!$K$4:$K$137)+SUMIF('Q-2'!$B$4:$B$137,B4,'Q-2'!$K$4:$K$137)+SUMIF('Q-3'!$B$4:$B$137,B4,'Q-3'!$K$4:$K$137)+SUMIF('Q-4'!$B$4:$B$137,B4,'Q-4'!$K$4:$K$137)

    How can i short this formula with same answer?
    i want to do total of 4 different sheets vlookup value.
    Thanks

  10. anybody help me please?
    i have data below…

    table1:
    ==========
    ITEM | TYPE
    ==========
    a1 | Cash
    a2 | Cash
    b1 | AP
    c1 | AR

    table2
    =============
    ITEM | AMOUNT
    =============
    a1 | 100
    b1 |-100
    a2 | 50
    a1 | 40
    b1 |-90
    c1 | 200

    result:
    =============
    TYPE | AMOUNT
    =============
    Cash | 190 (sumif?)
    AP |-190 (sumif?)
    AR | 200 (sumif?)

    how do I populate a 'sumif' formula in the result table?
    thanks & regards

  11. i have used simple sumif formula and it works
    as i have a table of values in one column (range 2) and criteria (range1) two and two cells out side as one for selecting the listed criteria and one for result on same sheet and applied the formula =sumif(range1, criteria, range2) in the cell where i expect the result.

    thanks

  12. Thank you very much for sharing.

  13. how to use iferror and vlookup in excel for counting

  14. Item Code Item Description
    A1 Processors
    A2 Printers
    A3 Motherboards
    This sample is belong to sheet1

    Item Code Item Description
    A1 vlookup formula pls!
    This sample is belong to sheet2

    what is the exact formula for the "item description" column in sheet number2, if I enter A1 on the "item code" column? the Processor should be the output.

    • Hello,

      If I understand your task correctly, please try the following formula:

      =VLOOKUP(A2,sheet1!$A$2:$B$4,2)

      Hope this will help.

  15. Item Code Item Description Item Code Item Description
    A1 Processors A1 vlookup formula pls.
    A2 Printers
    A3 Motherboards

    This sample is belong to sheet1 This sample is belong to sheet2

    what is the exact formula for the "item description" column in sheet number2, if I enter A1 on the "item code" column the Processor should be the output.

  16. I am looking to look up a product and calculate how much of it will be used.
    I have Thousands of rows and 25+ collums

    For example

    MAIN BREAKDOWN

    One & Half brick Walls
    Bricks 10000
    Cement 100
    Building Sand 1000

    One Brick Walls
    Bricks 12000
    Cement 120
    Building Sand 1200

    Half Brick Walls
    Bricks 2500
    Cement 15
    Building Sand 15

    Half Brick Walls
    Bricks 2500
    Cement 15
    Building Sand 15

    Half Brick Walls
    Bricks 2500
    Cement 15
    Building Sand 15

    BILL OF QUANTITIES (Seperate Sheet)

    Bricks Sum of all Bricks
    Cement Sum of all Cement
    Building Sand Sum of all Building Sand

    I need to add all of those up with out individually summing each one
    There are over 200 rows that mention bricks
    and we continually add in at various points of the sheet.

    PLEASE HELP!

    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  17. in MS excel If there are three columns A, B and C, I need to get their sums to a new sheet which ones heading comes as Column B details Look up one as column A, need to create formula to get sum for column C.

  18. in MS excel If there are three columns A, B and C, I need to get their sums to a new sheet which ones heading comes as Column B details Look up one as column A, need to create formula to get sum for column C.

    A 10 25
    A 8 30
    B 5 45
    A 10 30

    10 8 5
    A 55(Formula for this Number)

  19. Hello,

    I need to do almost exactly what you describe Under LOOKUP AND SUM - look up in array and sum matching values. However, while my lookup table is in fact vertical, my main table is horizontal. Hence, the formula you provide: {=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))}, does not work.

    Any ideas?

    Thanks!

  20. I want to lookup the 593027761 value in Sheet 1 and sum of all the values in
    Sheet 2 for lookup value in Sheet 1

    Workbook 1

    593027761

    Workbook 2

    593027761 100
    593027761 200
    593027761 600
    593027761 5485
    593027761 8784
    593027761 2544
    593027761 4898
    593027761 544
    593027761 54887
    593027761 216564
    593027761 5487
    593027761 54656
    593027761 553
    593027761 221

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)