How to use VLOOKUP & 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.

Suppose, you have a product list with sales figures for several months, a column per each month.

Source data - 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 sample VLOOKUP formula:

=SUM(VLOOKUP(lookup value, lookup range, {2,3,4}, FALSE))

As you see, we use an array {2,3,4} 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 rather than mere ranges. 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 that meet certain 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 Wizard add-in 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 : )

You may also be interested in:


295 Responses to "How to use VLOOKUP & SUM or SUMIF functions in Excel"

  1. Jay says:

    Want to do vlookup with checking lookup values and if dupilcates values find then it balance the duplicates values,else it write as single values.

    E.g. A B Qty output
    a1 d123 1500 1000
    a2 d123 700 1000
    a3 d123 400 600
    a4 d124 200 200
    a5 d125 300 300

    • Mihna says:

      A) Complete the cost, subtotal and total of the follwing table using the corresponding formulas.

      "B) Introduce today's date using the corresponding formula next to the cell ""Cost"" "

      C) Change the currency from euros to Pound Sterling

      Quantity Description Unit Price Cost
      3 Valve 1,500 €
      5 Column 500,000 €
      6 Clamp 980 €
      2 Turbine 200,000 €
      1 Compressor 100,000 €
      2 Tank 300,500 €
      3 Tube 6,750 €

      VAT 21%

  2. imran says:

    I have many columns like; date, ID, fullname, customer_type, any_details

    Now I want to make a form to add/ edit/ search/ delete data, Some time I would have to sum up customer type in a specific dates or month or year.

  3. Paul says:

    Column Column Column
    A B C
    Karim 100 200
    Rohim 200 300
    Karim 100 500
    Rohim 500 100

    Result should be:

    Karim 900 (100+200+100+500)
    Rohim 1100

    I used = sumproduct(Vlookup (A1,A1:C4,{2,3},0))
    But it does not work. How can I do it? Pls help

    • Krishna Tamrakar says:

      Yes! Paul, Same Problem With Me :), And I Need Distinct Name in Drop-down List
      A B C
      1 2019.12.02 Ronaldo 50
      2 2019.12.02 Messi 20
      3 2019.12.03 Ronaldo 50
      4 2019.12.03 Messi 22
      5 2019.12.04 Pele 15
      6 2019.12.04 Ronaldo 44

  4. Zahirul says:

    I need tinmtime sheet calculate with vlookup&sum please help me

  5. Juhil Lapsiwala says:

    I have applied this in rec.sheet and value voolup sum from 3 sheets.
    This works fine but need better way.
    Plz give me better suggestion

  6. Ana Domingo says:


    I have the chart below

    Employee name:
    Sick Taken :
    Vacation Taken

    Staff Name Type # of days From To Notes
    Josephine Domingo Vacation 1.0 1-Jan-18 1-Jan-18 Approved
    May Flower Vacation 0.5 1-Jan-18 1-Jan-18 Approved
    Josephine Domingo Sick 1.0 1-Jan-18 1-Jan-18 Approved
    May Flower Vacation 3.0 1-Jan-18 1-Jan-18 Approved
    Josephine Domingo Vacation 4.0 1-Jan-18 1-Jan-18 Approved

    What formula can I use to calculate the sum of Vacation days or Sicks days for a specific employee?

    Any help will be helpful :)

  7. Asher says:

    I need to get the values of (A1 Staff costs) in by month in below table. please help me

    12/20/18 A1 Staff costs 4,412.00
    12/20/18 A1 Staff costs 30,232.00
    12/20/18 A1 Staff costs 4,412.00
    12/20/18 A1 Staff costs 141,000.00
    01/10/19 B6 Travel 124.25
    01/10/19 B6 Travel 3,890.19
    01/10/19 B6 Travel 570.00
    01/10/19 B6 Travel 401.00
    01/10/19 B6 Travel 3,381.50
    01/10/19 A1 Staff costs 6,300.00
    01/10/19 A1 Staff costs 7,613.66

  8. Asher says:

    I need to get the values of (A1 Staff costs) in by month in below table. please help me

    12/20/18 A1 Staff costs 4,412.00
    12/20/18 A1 Staff costs 30,232.00
    12/20/18 A1 Staff costs 4,412.00
    12/20/18 A1 Staff costs 141,000.00
    01/10/19 B6 Travel 124.25
    01/10/19 B6 Travel 3,890.19
    01/10/19 B6 Travel 570.00
    01/10/19 B6 Travel 401.00
    01/10/19 B6 Travel 3,381.50
    01/10/19 A1 Staff costs 6,300.00
    01/10/19 A1 Staff costs 7,613.66

  9. Christian says:

    I need assistance with the following please:
    In one cell, I have an addition of: ((0)+(0)+(10))+((13)+(0)+(0))+((0)+(0)+(10)).
    I would like to only add the addition of the third parenthesis from every outter parenthesis so the total would give me 20.
    Do you guys have any suggestion for this approach.
    Thank you.

  10. Chase says:

    I am trying to use VLOOKUP and SUM/SUMIF to sum all the values in one column based on criteria in the first column. Example:
    Column 1 Column 2
    Avocado 10
    Roasted Chicken 10
    Mozzarella 5
    Roasted Chicken 5

    I need to sum column 2's number of Every instance of Roasted Chicken.
    using (SUM(VLOOKUP(A4,'PI - MLK '!$A$2:$2$5,2,FALSE))) I only receive the value of the first roasted chicken. How can I capture every instance?


  11. Olafur says:

    I have an area that I use to lookup entries from a table with data validation. The values selected I want to use to lookup and sum values from another table. I want to:
    Lookup these "Item" using data validation
    Item 7
    Item 8
    Item 7

    Then look in this table for each Col value selected
    XL L M S XS
    Item 7 1 1 2 3 0
    Item 8 2 3 3 0 0
    -Item 14 0 2 1 3 8
    Item 7 0 2 0 3 12

    and sum various sizes here from table above
    XL L M S XS
    4 4 5 3 0

    Can I use lookup and sumifs in an array formula?

  12. DrKrypton says:

    I have 2 sheets. the first (key and total$) has a list of product numbers in the first column. in column "I" I want to place the calculation that gets the product number from column "A", goes to sheet2, matches the product number in column "B" (there are duplicate product numbers), then grabs the corresponding $'s from column "R" (sheet2). and if there is a duplicate product number on sheet2 it will sum them together. I went through your sumif/vlookup instructions and cant get it to work. all help will be appreciated.

  13. Amarjit Sharma says:

    Dear Sir,

    12% 11733.12
    5% 2095.20
    12% 2304.72
    5% 5238.00

    5% = Total Value (In Single Cell)
    12% = Total Value (In Single Cell)

    How to sum if same types Tax % Value is calculating in multiple cell in excel single cell Tax % Wise

    Please help us.

  14. Amanda S. says:

    Thank you SO much for this! Helped me immensely in a complicated sheet today :)

  15. Syed Shafi says:

    sheet 1 has full details of all issuing item for the month want to look up sheet 2 as each item how many times issued particular item as one total
    Sheet 1
    Col A = Item Number( Same item Repeat many days(1001,1001,1002,1001,1003,1001)
    Col B= Qty issued (Repeat Many Days)
    Need Sheet 2
    Col A = Item Number ( 1000,1001,1002,1004, etc.)
    Col B = Subtotal of Qty issued for particular Item
    his it possible in Vlook Up , If So please update formula

  16. AHC says:

    INV001 1
    04-May-19 INV001 1
    14-May-19 INV001 2
    18-May-19 INV001 1


  17. satyadar says:

    Please somebody tell me how to create formula in excel for my data. Firstly It needs to find the matching word in that column and need to sum adjacent cell data of matching word.


  18. sam says:

    i want to add the values under a same ID in a particular column. Kindly give me the right formula.

  19. Rajesh Pati says:

    I want to make data like as below mentioned can anybody please help ??

    Sheet -1
    Modern Trade 16344.00
    Modern Trade 8847.00
    Modern Trade -23402.00
    Modern Trade 715.00
    DSE002 Dilli 3574.00
    DSE002 Dilli 18352.00
    DSE002 Dilli 16046.00
    Manoranjan S -16344.00
    Manoranjan S 16361.00
    Manoranjan S 3079.00
    Sub-D 726.00
    Sub-D 1054.00
    Sub-D 7841.00

    Sheet -2
    S.MAN Total sale Total -ve sale
    Modern Trade
    DSE002 Dilli
    Manoranjan S

  20. bal says:

    Need to Calculate Above 45 Days Value Total Sum from Next Sheet Row it is possible in formula?

  21. sivuyile says:

    i have a problem were I need to lookup sales made by a store for certain date and for a certain date there are various sales made by the various store and I need to add all sales made by a certain for a certain date and there a various stores eg
    date store

  22. Joahne says:

    I want to get the sum of specific brand to a specific store. Below illustration, please help:
    Worksheet 1: Table
    Branch Name: Glorietta
    Brand Name MTD2018 MTD2019 %Growth
    RJ Baby Cologne
    RJ Baby Oil
    RJ Baby Powder

    Worksheet2: Contains the datas
    *should i change the branch name, it will only show the sales for the specific store.
    Thanks for the help.
    God Bless and more power

  23. Geoffrey Meyer says:

    I'm trying to build a summation table totaling tuition spent on a client's grandchildren for the past 10 years. I have an annual sheet detailing the institution and month that tuition was paid for each grandchild, and I tried stringing your SUM/VLOOKUP formula together with a "+", but that blew it up and resulted with just the figures from the first column.

  24. Imran Masud says:

    Thanks, Svetlana!! for sharing with us.

  25. Muhammad Farooq says:

    Apple 50
    Banana 40
    Apple 50
    Banana 50
    Banana 40
    Apple 50

  26. Bhavik Mistry says:


    I have a Question.
    I have some Values in column A, and against them, I have some Numerical Values in Column B. But the Values in Column A are Repeating. So, I want Total of Repeating Values as a unique one in another column against each value. See below example for understanding:

    This is what I have:
    A B
    ABC 12
    xyz 14
    ABC 8
    tuv 15
    xyz 16

    I want Total Like this:

    C D
    ABC 20 (12+8)
    xyz 30 (14+16)
    tuv 15

    How it can be done?
    Kindly Explain


    • Hello Bhavik,
      Please paste the following formula in D1:

      =IF(COUNTIF($C$1:C1, C1)=1, SUMPRODUCT(--($A$1:$A$5=C1), $B$1:$B$5), "")

      And then copy it down the column D. Hope it'll help.

    • Viji says:

      =sumifs(select the answer column,from the same table select name column,now select one cell of the name column) and enter
      AB 0
      ABC 12
      XYZ 14
      ABC 8
      TUV 15
      XYZ 16
      AB =SUMIFS(B1:B6,A1:A6,A8)
      ABC 20
      XYZ 30
      TUV 15

  27. Krishna Tamrakar says:

    Dear Sir, I will be very grateful if you help me to
    Distinct dropdown list name and correstponding total using vlookup
    A B C
    1 2019.12.02 Ronaldo 50
    2 2019.12.02 Messi 20
    3 2019.12.03 Ronaldo 50
    4 2019.12.03 Messi 22
    5 2019.12.04 Pele 15
    6 2019.12.04 Ronaldo 44

  28. Krishna Tamrakar says:

    Dear Friends, I Will Be Very Grateful If You Help Me to show Distinct Name in Drop-down List and Total of Corresponding using Vlookup
    Thank You in Advance :)

    A B C
    1 2019.12.02 Ronaldo 50
    2 2019.12.02 Messi 20
    3 2019.12.03 Ronaldo 50
    4 2019.12.03 Messi 22
    5 2019.12.04 Pele 15
    6 2019.12.04 Ronaldo 44

  29. Nate says:

    I have categories instead of names or IDs. I need each instance of a specific category name added. For example, if I have 3 categories: Gas, Misc and Other. Every instance of each of those has a dollar amount, like so:
    Gas - $1
    Misc - $1
    Other - $1
    Misc - $1
    Gas total would be $1, Misc total would be $2 and Other total would be $1
    I need a list of each Category and the total dollar amount of every instance. I used this formula but it only represents the very first instances amount. Instead of all instances.
    =SUMIF(C14:C100, VLOOKUP($H4$2, Lookup_table, 3, FALSE), A14:A100)

  30. Sheikh Rahber says:

    1 CEMENT PPC 10 10 20 10 10
    2 CEMENT PPC 0 10 20 10 10

  31. Leo says:

    In sheet1 I have a fixed list of customer names and particular range of date of delivery.
    In sheet2 I have the similar customer names and drivers names with their delivery dates as well.
    my requirement is, I need to pull the drivers names along with corresponding customer names as per the each date in sheet1.
    Will you help me?

  32. Ramamohana says:

    How to get the sum by using vookup formulae

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard