Comments on: 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. Continue reading →

Comments page 3. Total comments: 441

  1. Greetings to you.
    I have a download of a bank statement where I received payment from different customers on one sheet and another sheet I have details of outstanding for the customers. how do I reconcile to see the outstanding on the details of outstanding for customers?
    I want to bring the sum of payments received for each customer on the other sheet nest to their name.
    thanks

    1. Hello!
      You have provided very little data so that I can give you specific advice. I think you can use the SUMIF function. Read more here.

  2. Can you share a video on it step by step

  3. Hi!
    Can you please help with the following:

    i have rows with months (column B1) (Jan, Feb, Mar, Apr, May) - around 200 quantity rows for each month
    rows with number of vehicles sold (column C1) - it is mixed, in disarray
    rows with revenues (column D1) -
    rows with vehicle names (column E1) -

    Data is duplicated here. I am allowed to USE A SINGLE FORMULA to show how many cars were sold in each month for each vehicle brand. And this formula should create a table by itself, not manually!
    Could you please advise?
    Thanks a lot!

    1. Hello Kirill!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Specify what data you want to receive. Need to calculate the amount of sales for a specific month for a particular car? Then you need a list of months and a list of cars. Which table do you want with one formula?

  4. I have two cells in Excel (A1 and B1). A1 is supposed to be changed every month with different values depending on sales. and B1 has an existing value of $100.

    For the month of August, the sale is $150 (which shall be entered in A1 cell). I want to add this $150 to the existing $100 in cell B1 to become $250 (which is very simple).

    For the next month; however, I want to enter a different value in cell A1; say $200, and I want this $200 to be added to the $250 in cell B1 to become $450.

    I don't want a copy and paste. In the same cell specified, I want to enter different values while the value entered every time shall be added to the value in cell B1.

    I wonder if I can do this in Excel.

  5. How to subtract the available stock if the same item of product repeatedly sells. After every sale to show available stock in the corresponding cell.

      1. I have two cells in Excel (A1 and B1). A1 is supposed to be changed every month with different values depending on sales. and B1 has an existing value of $100.

        For the month of August, the sale is $150 (which shall be entered in A1 cell). I want to add this $150 to the existing $100 in cell B1 to become $250 (which is very simple).

        For the next month; however, I want to enter a different value in cell A1; say $200, and I want this $200 to be added to the $250 in cell B1 to become $450.

        I don't want a copy and paste. In the same cell specified, I want to enter different values while the value entered every time shall be added to the value in cell B1.

        Can you help me to solve this?

      2. Thanks, Mr.Alexander Trifuntov, thanks for your valuable reply.

  6. Alexander,
    I'm trying to sum a random section of a column that all have the same ID. I can get it to work summing N15,N22 but I need N15:N22. Column B contains a common ID for rows 15 through 22.
    SUM(N15:N22) ~ works great
    VLOOKUP(B22,B15:P22,13,0) ~ works great
    SUM(VLOOKUP(B22,B15:P22,13,0),N22) ~ works great
    SUM(VLOOKUP(B22,B15:P22,13,0):N22) ~ does not work

    1. Hello Jeff!
      The VLOOKUP function searches only the first match. Therefore, your formulas will not give the correct result. If the name from column B is known exactly, then use a simple formula

      = SUMIF (B1: B50, "Geoff", N1: N50)

      If you need any further assistance, please don’t hesitate to ask.

      1. Thank you, my approach was wrong and the SUMIF function works. I’m still curious as to why this works: SUM(VLOOKUP(B22,B15:P22,13,0),N22), giving me the sum of N15 + N22, but this does not work: SUM(VLOOKUP(B22,B15:P22,13,0):N22). I would think that if the code accepts the comma (N15,N22), then the code would accept the colon (N15:N22). Clarification would be great, Thanks.

        1. Hello Jeff!
          In any Excel function, the address of the data area (for example, A1: C30) can only be transferred using the INDIRECT function.
          Therefore, SUM (VLOOKUP (B22, B15: P22,13,0): N22) does not work.
          The SUM formula (VLOOKUP (B22, B15: P22,13,0), N22) works, because for the SUM function it’s just 2 numbers separated by a comma

          1. Great, thanks Alexander for the clarification.

  7. I have a table with two columns. One with a name (repeated multiple times potentially within that column) and a number of hours in the column next to it. I want to sum all of the hours together for each particular name.
    For example, Geoff appears in column 1 three times with hours in the second column of 5, 7 and 9 respectively. How do I sum together all of the second column for Geoff, please?

  8. Hi! I'm trying to use the sumif & vlookup for something very similar but slightly different and I'm not sure how to modify the formula.

    I don't have a lookup table matching IDs to specific names. I'm trying to create a sheet that just lists the sum totals costs for specific IDs. The costs are within a data export on a separate sheet/tab and are randomly listed across the sheet (much like your example).

  9. I have text values in A:A and corresponding number values in B:B. Want to enter multiple values from A:A in C1 separated by a comma. Want the corresponding sum of value in B:B based on multiple entries in C1.

    Please help with formula

    1. Hello Siddisi!
      If you have the text values in column A, the number values - in column B, and the list of values – in cell C1, you can use the following formula:

      =VLOOKUP(LEFT(C1,FIND("#",SUBSTITUTE(C1,",","#",1))-1),A1:B5,2,0) +IFERROR(VLOOKUP(SUBSTITUTE( LEFT(C1,FIND("#",SUBSTITUTE(C1,",","#",2))-1), LEFT(C1,FIND("#",SUBSTITUTE(C1,",","#",1))-1)&",",""),A1:B5,2,0),0) +VLOOKUP(RIGHT(C1,FIND("#",SUBSTITUTE(C1,",","#",1))-1),A1:B5,2,0)

      Or

      =INDEX(B1:B5,MATCH(LEFT(C1,FIND("#", SUBSTITUTE(C1,",","#",1))-1),A1:A5,0)) +IFERROR(INDEX(B1:B5,MATCH( SUBSTITUTE(LEFT(C1,FIND("#",SUBSTITUTE(C1,",","#",2))-1), LEFT(C1,FIND("#",SUBSTITUTE(C1,",","#",1))-1)&",",""),A1:A5,0)),0) +INDEX(B1:B5,MATCH(RIGHT(C1,FIND("#",SUBSTITUTE(C1,",","#",1))-1),A1:A5,0))

      In this case the text in C1 looks like abscdc,qwerty or asdfg,zxcvb,qwertyyu, i.e. these are 2 or 3 text values separated by a comma without spaces.

  10. Sir,
    How to get the sum by using vookup formulae

  11. Hi,
    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?

  12. S.NO.|ITEM GROUP|ITEM NAME|OPENING QTY|RECEIVED QTY|TOTAL|ISSUE QTY|BALANCE QTY|UNIT
    1 CEMENT PPC 10 10 20 10 10
    2 CEMENT PPC 0 10 20 10 10
    I WANT THIS 0 QTY CALCULATE FROM PARTICULAR ITEM BALANCE QTY

  13. 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)

  14. 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

  15. 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

  16. Hello,

    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

    Thanks,
    Bhavik

    1. =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
      Answer
      AB =SUMIFS(B1:B6,A1:A6,A8)
      ABC 20
      XYZ 30
      TUV 15

    2. 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.

      1. Thank You So Much Ms. Mary. :)

  17. Apple 50
    Banana 40
    Apple 50
    Banana 50
    Banana 40
    Apple 50

  18. Thanks, Svetlana!! for sharing with us.

  19. 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.

  20. Sir,
    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

  21. 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. Need to Calculate Above 45 Days Value Total Sum from Next Sheet Row it is possible in formula?

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

    Sheet -1
    S.MAN NET AMT.
    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
    Sub-D

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

  25. Hello,
    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.

    Thanks

  26. 1ST SHEET
    MATERIAL CODE USAGE
    INV001 1
    2ND SHEET
    DATE ISSUANCE MATERIAL NO. QTY
    04-May-19 INV001 1
    14-May-19 INV001 2
    18-May-19 INV001 1

    I USE =SUM(VLOOKUP([@[MATERIAL CODE]],USAGE!C:E,3,1)) TO TOTAL ALL INV001 IN 1ST SHEET BUT ONLY 1 IS THE RESULT.WHAT FORMULA CAN I USE TO TOTAL ALL INV001 IN 1ST SHEET AFTER I UPDATE THE 2ND SHEET.PLEASE HELP THANK YOU.

  27. 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

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

  29. Dear Sir,

    GST TAX% TAXABLE VAL.
    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.

  30. 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.

  31. Hi.
    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?
    Thanks,
    Olafur

  32. Hello!
    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?

    Thanks,
    Chase

    1. check out the sumif function

  33. Hello,
    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.

  34. please
    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

  35. 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

  36. Hi,

    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 :)

  37. Sir,
    I have applied this in rec.sheet and value voolup sum from 3 sheets.
    =SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-1'!$J$4:$J$142)+SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-2'!$J$4:$J$142)+SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-2'!$J$4:$J$142)+SUMIF(Rec.Sheet!$B$4:$B$144,B4,'Q-3'!$J$4:$J$142)
    This works fine but need better way.
    Plz give me better suggestion

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

  39. 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

    1. 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

      1. use data validation function

  40. 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.

  41. 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

    1. 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 €

      Subtotal
      VAT 21%
      TOTAL

  42. 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?

  43. 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

    1. 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")

  44. 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

  45. 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.

  46. 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.

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

  47. 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".

  48. 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.

  49. Hi

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

  50. =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

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 :)