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 8. Total comments: 441

  1. Hi all

    Could you please tell me how in range of say some amounts, how to arrive at a particular sum amount. Suppose I have 50 Invoice amount due from a customer. I received a payment which includes 10-20 invoices. how can i know which amounts add to that particular cheque amount. Could you please give me the formula. Thanks

  2. how to use sum and v lookup farmula from multiple rows
    BANK 301370
    BANK 736310
    BANK 1473600
    CONST. 100
    CONST. 200
    CONST. 200
    CONST. 200
    CONST. 200
    CONST. 218
    CONST. 300
    CONST. 29000
    CONST. 50000
    CONST. 68800
    FREIGHT 50
    FREIGHT 200
    FREIGHT 450
    GENERAL 50
    GENERAL 200
    GENERAL 220
    GENERAL 250
    GENERAL 300
    GENERAL 380

    i want sprate total like summery

  3. Hi, I hope someone can help. I would like to add up 2 sets of 2 columns with multiple codes for certain results.

    For example/

    Column A (company) consists of:#1 and #0
    Column B (company) consists of:for #1 - A,B,C,D,Z
    for #0 - F,G,H,I,J,K,L,M

    Column D (contact) consists of: #0, #1 and #2
    Column E (contact) consists of: for #0 - A, B
    for #1 - C, D, E, F, G, J
    for #2 - C,D,E,F,G,I,K,L,Z

    Thanks in advance

  4. Hello Dear,

    i know sumif formula. But i don't no vlookup sum formula that means ID Value Total. for Example,
    Sheet1,
    ID Value
    A1 100
    B1 200
    C1 300
    A1 500
    B1 300
    C1 400

    and i needed ID wise sum A1=100+500=600.
    B1=200+300=500.

    Please help me.

  5. I have a spreadsheet for monthly/yearly overtime worked per job position at my company. People can also be charged time and a half or time and three quarters, depending on their schedule.

    I need a way to search column b for job position and column c for type of schedule and if those equal PositionA ScheduleA to add column f's numbers all together in Cell Y. If PositionA Schedule B, add them together in Cell Z.

    Is that too complicated for Excel?

  6. Hello,

    Is it possible to sum a row of numbers only if those numbers are from a formula? I am using a forecasting spreadsheet and any future figures are input manually by the budget managers as a 'guess'. However the actual spends for each month up until that point are found by using a VLOOKUP formula.

    Basically I want to only sum the numbers if they are from the VLOOKUP formula.

    Thank you for any help you can provide.

  7. Hi All

    I hope you are all well.

    I don't know if this is going to be a bit of a stupid question, and I hope I make myself as understandable as possible. :)

    Firstly, I am using Excel 2016 on Windows 10.

    This is the scenario:

    Book 1

    - I have one Excel workbook with individual invoices for multiple customers. For example, the most important info:

    Column A - Customer name (There are sometimes more than a few invoices for one customer)

    Column B - Date

    Column C - Invoice amount

    Book 2

    - I have another Excel workbook with the same as above, but they are credits relating to those invoices/customers:

    Column A - Customer name (Same as above - more than a few lines for one customer, many different customers)

    Column B - Date

    Column C - Credit amount

    Book 3

    - The other Excel workbook is a consolidation of the above. I need to consolidate the above by customer, by month (working on November 2015 to October 2016 MTD). I considered doing a VLOOKUP by customer, but I don't know how to do a SUM VLOOKUP to add together all invoices for one customer in a date range from Book 1 onto Book 3, then the same for the credits from Book 2 to Book 3, so that I can calculate the difference.

    I hope someone can assist me with this, and that I have made the question quite clear. :)

    Thanks in advance!

  8. How can I remove N/A

    Sheet 1 Sheet2 Sheet 3 Sheet 4
    As=(Vloopup)+(Vlookup)+(Vlookup)+(Vloopup) from different sheet, one sheet have no value(Like Sheet 4), but other sheet have value(Sheet 1,2,3) its showing N/A. If I use IF(ISERROR(Vlookup), then showing 0. But its not true.How can i solve this problem

    Urgent

  9. Hi,
    I am trying to combine a SUMIFS and a VLOOKUP. My data set is big, and I run into problems when a name that used to be in the data is deleted from the data. That is why I want the forumla to lookup at the ID number of the person in my spreadsheet first, and then match it with their value in the data tab. Some people are in there twice, same ID but different two different values. I was a forumala that will match to the first and then match to the second.

    Can someone help with this?

  10. i have tried this with sumif(vlook up) but i dont get the esult and i cudnt find my error in formula

  11. Hi
    I am trying to do a sumif. The criteria are on two separate tabs. I want to sum if the attribute is "adjustable", but this attribute is on a separate tab so I have to do a vlookup to see if it is adjustable or not. Once something is adjustable, then it should be summed up. I've tried the following two formulae, and they don't seem to work:

    =SUMIFS('LG dataset Subfund Method'!I$2:I$611,vlookup('LG dataset Subfund Method'!F2,'Attributes Matrix'!$A$2:$G$611,7,false),Adjustable)

    and

    =SUMIFS('LG dataset Subfund Method'!I$2:I$611,INDEX('Attributes Matrix'!$G$2:$G$611,MATCH('Attributes Matrix'!A2,'Attributes Matrix'!$A$2:$A$611,0)),Adjustable)

    Basically I'm trying to create a logic that says sum up the cashflows on the LG tab only if the attribute attached to the plancode is Adjustable and this part is on the Attributes Matrix tab.

  12. Hi.,,

    May I Know How To Add Number with the Help of Vlook_Up..

    For Ex:

    Sharat
    Fruits Numbers
    Apple 190
    Banana 111
    Mango 503

    Geeta
    Mango 113
    Banana 190
    Apple 190
    watermelon 190

    Ram
    Banana 128
    Apple 147
    watermelon 180
    Mango 114

    ..

    ...

    How i Can Add Like......

    Fruits Total Items
    Total Banana ?
    Total Apple ?
    Total Mango ?
    Total watermelon ?

  13. Hi, I have a worksheet with a matrix of names (6 columns of differing names in each rwo) and in another column I have either "Won" or "Lost".

    I want to count the number of "Won"s for each person.

    So I have a formula: =COUNTIFS(TMT!$F$2:$K$29,B4) which finds and counts all the times the person's name in B4 exists. This works OK.
    But when I add another selection =COUNTIFS(TMT!$F$2:$N$29,B4,TMT!D:D,"=Won") I get an error "A value used in the formula is a wrong data type". However if I enter only the second selection on it's own I get no error.
    Any ideas?

    1. Dear Philip Morris just remove = of "=won" from =COUNTIFS(TMT!$F$2:$N$29,B4,TMT!D:D,"=Won")

  14. Hi,
    I have to find sum of sale qnty using vlookup

    first sheet where purchase is
    Code Qnty
    NFSC 2
    NFCC 5
    NFDD 7

    Other sheet where sale of the month is (date wise sale)
    Code Qnty
    NFSC 1
    NFCC 1
    NFDD 1
    NFSC 1
    NFDD 1
    NFCC 1

  15. Hi there,
    Your tutorial is good but i'm struggling to adapt it to my situation and wondered if you could help?
    I have a workbook for timesheets that contains 53 sheets, one for every week. Each sheet is formatted the same and shows the job number and job name in rows above one and other in the first column. These rows are then combined in the next column to show total hours worked, then the next columns represent the actual days of the week and hours worked per day.
    I would like to have a summary table at the end of the workbook that tells me total hours worked in the year and total hours spent per job number. i've tried a variety of sum and look up combinations and nothing has worked yet.
    any pointers you can make would be greatly appreciated.

  16. Respected Able Bits Team;
    Please give me merge tables wizard excel 2003 .xls format

    Regards
    Saurabh Sharma

  17. Hi,

    I have a fairly simple task that I just can't figure out a formula for it. I have a range of account # that I need to find and sum on one cell.

    For example,

    I need to sum account numbers 2001-2020. All accounts from 1000 to 3000 are listed in column B (in another tab within the same work book). Column C in that same tab has the account values that I need to sum.

    I tried the SUM-LOOKUP combo but sorting in ascending order is not an option for me. What other formula can I use to get to my desired result?

    I would really appreciate your help with this!

  18. Hello Svetlana,

    What you have shown is simply amazing and I sincerely appreciate the time and effort you have put. Thanks a lot!

    I have a small query which I believe has no solution but would like your opinion on. So I am working on a case similar to SUMIF and VLOOKUP combination example above, but the difference is that my lookup array is spread across multiple worksheet and not just one main table. Any thoughts on how to resolve it? I was thinking using SUMIFS and VLOOKUP maybe, but not sure! :-(

    Anky

  19. How to calculate catagory wise cummilative value like
    Mc. Qty
    Duke120. 3
    Duke 220. 4
    Deke 330. 3
    pulser 120. 5
    Pulser 220. 7
    Pulser 250. 8
    Now what im want is cummilative value of duke and cummilative value of pulser different wise but on a same column.

  20. when i create invoice ...then automatic stock credit or debit.,amount plus in customer account.,when enter part number then automatic fill part discription....i wish this formula

  21. Good Day! What is the formula for the =Sum(Vlookup) For vertical addition of data?

    Regards

    Tlhogi

  22. I have been struggling to fulfill my boss requirement but still unsuccessful. Maybe someone can help me with the quickest way.

    I have a workfile with multiple sheets but each sheets uses the same format. Example:
    Sheet 1.
    Part Name: ABC
    Type of Defects 1-May 2-May 3-May 4 May
    Defects 1 10 100 50 60
    Defects 2 12 50 20 30

    Sheet 2 or 3 or 4 are the same as sheet 1 except
    Part Names are different

    I want to create a Summary page whereby I just need to key in the following criteria:

    Part Name : XXXX ( just key in the part number )
    Date Start : Select which start date
    Date End : Select which end date

    With this 2 criteria typed in, all the defects will automatically add up but still separated still into Defects 1 and Defects 2.

    In other words, the left hand column will still have Defects 1 and Defects 2 and so on.

    Can someone help me?

  23. Hye,

    I'm meeting trouble in doing sum of different vlookup values. I try explain my problem with an example given below:

    | A |B |C |D |E |F
    1| 02-01 |48 |22 |38 |54 |98
    2| 03-01 |20 |105|111|50 |40
    3| 03-02 |35 |40 |67 |66 |901
    4| 03-03 |88 |50 |100|47 |200

    In given above table I want to sum all values vertically in column F with reference to Column A but want to add only that values of which code in Column A start with 03.

    Please guide me. You can mail reply on my mail ID if convenient. Thanks

  24. i want lookup A3 value in range B3:B10 if cell value verify then result show with text and commas

  25. one problem mostly i face when i update the advance of employee .the problem is . there are three column

    column(A) column(B) column(C)
    employee punch n. Advance
    mohit 54 1500

    mukesh 84 2500

    rahul 90 2000

    mohit 54 500

    sabir 92 5000

    is there any formula that add the amount that is double to a employee
    by a formula in excel sheet when i prepare salary and adjust the advance .

  26. Hi Svetlana,

    I have a data in which first row contains date and other three rows contains their pageviews, visits. I need your help to merge date wise data in which i can the total visits pageviews and as well as visitors for all dates seperately this database contains 4700 rows.

    Below are the example of data.

    Looking forward for your reply.

    Date Visitors Visits Page Views
    1-Jan-08 110,318 143,414 165,477
    1-Jan-08 111,056 144,372 166,583
    1-Jan-08 112,961 146,850 169,442
    1-Jan-08 110,977 144,271 166,466
    1-Jan-08 110,561 143,730 165,842
    1-Jan-08 105,094 136,622 157,641
    1-Jan-08 112,742 146,565 169,113
    1-Jan-08 108,948 141,632 163,422
    1-Jan-08 110,211 143,274 165,316
    1-Jan-08 107,731 140,050 161,596
    2-Jan-08 107,917 140,292 161,876
    2-Jan-08 109,908 142,880 164,862
    2-Jan-08 105,705 137,417 158,558
    2-Jan-08 106,791 138,829 160,187

    Thanks,
    Nandan

    1. You can use Paviot Table. It will surely convenient for you.
      Paviot Table can be added from Insert tab.

  27. how can we solve it by only using vlookup....

  28. Hi,

    I have 12 columns and thousands of rows. On columns I have (Years-Months-Factories-a-b-c-d-e-f-volume-sales-price)..What I want to do is, I want to calculate total volume for each factory(4 factory) by months in each year..

    Thx

  29. hi,
    I am using 2 sheets with daily sales in sheet1 and inventory in sheet2. I want to put sum formula in sheet2 so that it pick an item no and goes to sheet1, locates its occurrence and than calculate from values in corresponding columns. But the tricky part are, first, values are in 3 columns - sale(-), return(+) and stock delivery(+). Second, sale for same item will happen on multiple days, so all will be factored in and entered before the item in inventory list.
    I have input an array formula with sum and vlookup in sheet 2. It gives value for only one column and one row.

    Please help.

  30. i need to calculate the average value for male and female students in class. but their title in different page and final mark in different page. need to do it using vlookup..

  31. can i be able to do sum of the various values having base as sku code (ex:20001218 -1
    in the same i have the other one to )

  32. Hi,

    I have a excle worksheet. in that there are every month salary detail in different month sheet. I want to get total of salary of the year in one sheet against his/her name. How to do it with Vlookup formula. Kindly suggest me.

    Thanks...

  33. Great Forum for learning! really appreciate Svetlana & Teams efforts. Thanks!

  34. Hi
    Require a help

    I have a rows of values like this
    5000
    2000
    7000
    15161
    5000

    In another cell i have a value 25161

    In the rows if i add some way i should be getting 25161 ( example 5000+15161+5000) will match my 25161,

    Is there any formula to achieve this

  35. what formula i need to use for below

    sheet one - GL code, vendor name and Feb amount in column. One vendor has multiple GL codes
    Sheet two - Same has above but amount is for JAN
    I need to bring jan amount to sheet one next to feb amount to see the varience

    since one vendor has multiple gl codes just vlookup formula is not working. What i need to do here

  36. HI
    I want to count the value in subgroup for example:
    A
    B
    C
    d
    d
    d
    C
    d
    B
    C
    d
    d
    d

    I want to know how many "d" are in the first "C" group
    Or how many "d" are in the first "B" group
    "Count "d" from current value to same value in column.
    so tnx

  37. Hi- I need help on following table to combine vlookup and sumif.

    Table :

    A -2
    A 4
    A -3
    B -4
    A -4
    B 7

    I need formula to calculate only for negative sum from column 2 and vlookup for column.

    Results should be like this
    A -9
    B -4

    Can you help to create formula for this?
    Thanks, Rahul

    1. sent me in my email i well do for you.

  38. Dear Sir i have 3 sheets of diffrent subject having same table... and i want to find the result in one click... for example i want to find the result of pecentage on single cell by given roll number

    1. Formula Required if in a cell contains 10 digit Pan Number AKRPD3915C if the 4th Letter P it should show as "Person" and if the 4th Letter C it show as "Company"

  39. I need your help, I just want to one cell contain text in other cell list i think formula =ISNUMBER(FIND(E47,B3:B991)) in this E47 is my targeted cell

  40. helo,
    please I need your help.

    is there any formula that spell a number or any currency ???

    Appreciate your support

  41. Hello hope that someone can help me? i need a formula that allows me to sum up the hours worked a day, the problem is it needs to be by name for example.

    harry worked from 09:00 till 09:30
    carlos from 10:00 till 10:30.
    etc

    how can i make excel add the worked hours behind carlos his name?

    thank you

    this is for a working schedule that sums up the worked hours as well.

  42. i have some invoices in sheet1 and i want to make a summary with product name and Quantity in sheet2

    so please help me to solve this problem detils are

    PRODUCT QTY
    AREIL 45G 96
    AREIL 500G 12
    SAFEGUARD 115G W 72
    SAFEGUARD 115G L 144
    H&S 200ML CC 6
    H&S 200ML B 12
    PANTENE 90ML SS 24

    PRODUCT QTY
    JELLETT BLUE 2+ 24
    MACH 3 RAZOR 3
    AREIL 45G 24
    AREIL 1000G 6
    SAFEGUARD 115G W 144
    AREIL 500G 6

  43. What formula can return all possible combinations of numbers that add up to a given total. For example, let's say the given total is the number 137. Which combinations of numbers would add up to 137? To make this more interesting, no number can be repeated, and no more than 5 numbers can ever be used in one single try. So 7+35+45+49+1=137, but many more combination do as well. Thank you.

    1. Hello Steven,

      It is an interesting task, but I'm afraid we can't calculate it using formulas. It would be useful to find a Maths student with a good knowledge of VBA :)

  44. Hi Everyone and anyone, pls. if you know the formula to the given equation pls. share the answer... Thanks
    I have 3 sheets in each three sheets in column A is there are common and different no. how do i get these no. but NOT get repeated in my another "final" sheet,
    Second, i want to get the sum of each 3 sheets for column A representing in different cells data to Final sheet.
    So, for First result i tried =IFERROR(VLOOKUP('1st Month'!$D4,'2nd Month'!D4:D22,1,0),IFERROR(VLOOKUP('2nd Month'!$D4,'3rd Month'!D4:D23,1,0),IFERROR(VLOOKUP('1st Month'!$D4,'3rd Month'!D4:D25,1,0),"0"))) formula and not sure if this right.
    for second Result i Tried = SUMPRODUCT(SUMIF(INDIRECT(""&test&"''!$D$4:$D$19"),$D4,INDIRECT(""&"'!$I$4:$I$19")), Pls Help me in getting the expected answer. Thanks

    1. Hello Rahul,

      What concerns your first task, the easiest way would be copying all column A values from three sheets into your final sheet, removing duplicates, and then applying the VLOOKUP formula.

      As for summing the corresponding values from three sheets, it sounds like data consolidation is the best option for you. Please see this blog post for detailed steps:
      How to consolidate data from multiple worksheets

  45. Hi Maria,
    i have 3 sheets in each three sheets column A is the common and different no. how do i get these no. not get repeated in my another "final" sheet.
    Second, i want to get the sum of each 3 sheets column A representing data to Final sheet.
    So, for First result i tried =IFERROR(VLOOKUP('1st Month'!$D4,'2nd Month'!D4:D22,1,0),IFERROR(VLOOKUP('2nd Month'!$D4,'3rd Month'!D4:D23,1,0),IFERROR(VLOOKUP('1st Month'!$D4,'3rd Month'!D4:D25,1,0),"0"))) formula and not sure if this right.
    for second Result i Tried = SUMPRODUCT(SUMIF(INDIRECT(""&test&"''!$D$4:$D$19"),$D4,INDIRECT(""&"'!$I$4:$I$19")), Pls Help me in getting the expected answer. Thanks

  46. Sir

    I need help about I have Two Spreed Sheet of Excel I want If Sheet1 Cell F1 is equal to or Greater than 19 then Copy Sheet1 Cell A1:D1 and paste the same in Sheet2 A1:D1 please help me for this formula i have urgently required

    1. Hello Nisar Ahmad,

      Please enter the formula below into cell A1 of Sheet 2:
      =if(Sheet1!$F$1>=19, Sheet1!A$1,””)

      Then copy the formula to all cells in the row: B1 to D1.

  47. Hi Jim,

    I have emailed you the excel sheet with the solution which would be more clear to you. Moreover I am not able to attach the excel file in this blog.

    Regards,

    Ramki

    1. Hello Ramki,

      I'm sorry, but we can't seem to find your email. Could you please make sure you sent it to support@ablebits.com? Please include the name of the blog post and the number of your comment.

  48. Good afternoon!
    I have a task in which I will be referring to two sheets. On the first sheet, I will have a name column (A1:A200) and a quantity column (B1:B200).
    On the second sheet, I will also have a name column and a quantity column, A and B respectively. My task is to search each name of sheet1!A1:A200 for a matching name on sheet2! Where there is a name match, I need to have the quantities added together. I hope you can assist me!
    thank you!

  49. Hi

    I have managed to get a sumif and combine it with a vlookup and that seems to give me the answers I need, but what I want to do is know when the values I've added reach a defined range of 335-395, and then return me the cell which falls in that range

    Any suggestions would be great!!

    Thanks

    1. Hello, Helen,

      To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  50. Below the letter A are the number 15, 23 and 15.
    Below the letter B are the number 18, 13 and 20.
    Below the letter C are the number 50, 19 and 30.

    Thanks.

    1. Hello, Dung,

      You need an array formula:
      =SUM(IF(ISERROR(OFFSET(A2:C6, -1, 0)), 0, IF((OFFSET(A2:C6, -1, 0))="A", 1, 0) ) * (IF(ISNUMBER(A2:C6), A2:C6, 0)))

      Your data are in A1:C6. Use the same formula for B and C, just change ="A" correspondingly.

      Make sure you press CTRL+SHIFT+ENTER after entering the formula.

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