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

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

  2. hi
    i need a function for the mentioned query

    NAME SEGMENT TARGET
    A R 2
    A R 3
    A R 2
    B R 3
    B BB 4
    B R 2
    A BB 1
    A R 3

    NAME A
    NAME WISE TOTAL :
    NAME WISE SEGMENT TOTAL :

    Please suggest NAME WISE TOTAL Required and NAME with SEGMENT wise total required.

  3. It is very very helpful for me.

  4. i need to find name with address and amount and also date wise
    main sheet is PT NAME with amount and date and ADDRESS DATA is second sheet with address and third sheet is required sheet with VLOOKUP FORMULA but i am not co relate with amount and date kindly help me for solving the problem.

  5. I want to apply vlookup formula in excel sheet having pt. name and address and also i having data only pt. name how can match pt name with colour code?

    1. I want to apply vlookup (or any) formula in excel sheet having pt. name and address and also i having data only pt. name how can match pt name with colour code?

      09/11 10:18: Mast Devesh Aahuja Mast DEVESH AAHUJA Mast DEVESH AAHUJA 4 Y Dr. Asim Negi 44 tilak path khargone
      13/12 12:48: Mast Dhananjay Magwani Mast DEVESH AAHUJA Mast DHANANJAY MAGWANI 15 Y Dr. Jitendra Pindoriya gram kampel teh. kampel
      19/10 17:03: Mast Divyanshu Prajapat Mast DHANANJAY MAGWANI #N/A 45 Y Dr. Jitendra Thakur gram fangti teh. hatpipliya
      12/10 20:55: Mast Gajendra Yadav Mast DHANANJAY MAGWANI #N/A 13 Y Dr. M.K Sharma gram mogawa teh. maheshwar
      kindly help me.

  6. Dear Svetlana Cheusheva,
    Can I sent an excel Sheet with data to make Formula to calculating
    the Data with criteria
    regards

  7. Hello Svetlana,

    I'm looking to get the MAXIMUM of the multiple LOOKUP VALUES which are scattered and which are in different worksheets.

    I tried with vlookup and index / match but could not do.

    Example:

    The answer when I try to vlookup for A0600 should be 1700 and not 1692 from the two lines given below:

    A0600 23099090 XXXX 1692.00
    A0600 23099090 XXXX 1700.00

    Thank you so much in advance for your help!

    Thiyagu.

  8. I need some help. I have several tabs in an excel file with amounts per person (i.e. John $35, Kathy $40). Each person has a separate identifier (ID). I need to add all of John's amounts into one spreadsheet, all of Kathy's, etc. John may be in tab 1 and on tab 2 but not on tab 3. I think I need to use some vlookup combined with a sum but I can't figure out what to use. Can you please help me?
    Thank you!

  9. Country 2011 2012 2013 2014 2015
    Japan 1653 1232 5319 9230 7647
    Ukraine 5582 7685 7706 3723 6181
    Japan 7330 5872 6723 6133 3228
    Poland 4063 4337 4916 7608 4451
    Germany 2330 3906 4673 1276 1592
    Ukraine 7396 1752 8262 9823 9164
    Germany 6690 5303 5155 4422 7661
    Poland 1170 5878 6089 2398 3338

    This is the date, now, for example, I want to use sumifs formula to calculate the sum of sales for Japan after 2012. Can you help? I tried the formula "=SUMIFS(B2:F9;A2:A9;G3;B1:F1;">"&G4)" but it did not work.

  10. Hi,

    Anyone can help for the formula of sumif. i got a total sum figure like 206.61.
    Can the sumif formula find out from the column which of the value add on together is equal to 206.61.

    Thank you.

  11. Hi,

    I was trying to make customers outstanding in 30-60 , 60-90 days old ,

    i have the following sheet (With Formula),
    A b c d e f
    Name , Invoice date, Invoice No, Invoice amt , Balance, Due days
    PICO 11/05/2017 653 3000.00 3000.00 75

    I want to vie as below
    a b
    30-60 days, 60-90 days
    3000.00

    Can you help??/

  12. HAVE SHEET CONTAINING EMPLOYEEID, GENDER, AGE, NO OF CHILDREN ETC.
    ANOTHER SHEET CONTAINS EMPLOYEE ID,SALARY, LOANS ANY OTHER SIMILAR COLUMNS.

    IN THE THE SECOND SHEET I WANT THE SUM OF SALARIES OF EMPLOYEES WHOSE AGE IS ,SAY 45,. I DONT WANT TO IMPORT THE AGE FROM SHEET1 USING LOOKUP AND THEN APPLYING SUMIF.
    PLEASE SUGGEST ANY FORMULA SYNTAX WHICH WILL CHECK THE CRITERIA AN RETURN THE SUM OF SALARIES OF SUCH PEOPLE WHOSE AGE IS 45.

  13. Sorry hit enter too quickly here is the example
    Column A Column B Total
    a 10 50
    b 20
    c 10
    b 10
    c 10
    a 20

    What i want is a function that will look at column a and add only the values in column b that are coded as b,c ie 50. Therefore if my next entries 3 entries are coded a and my fourth entry is code b with a value of 10 the function should ignore the ones coded a and add 10 to the total to get 60.

    i hope you can help.
    Thanks

  14. I have a table that i populate every day. I want to calculate the total number of articles that are added to the previous day but only the ones that have a specific code. See example

  15. Hi Svetlana

    In below table array I need to sum quantity of repetitive items (same coded in column A)so they appear in new column as sampled below.
    Could you please advise.
    Many thanks
    Pavel
    A B C D
    10052160 252.00 Pound 262.00
    10052160 10.00 Pound 0.00
    10072070 187.66 Pound 187.66
    10072081 321.64 Pound 321.64
    10072089 643.35 Pound 643.35
    10072100 41.00 Can 41.00
    10072111 30.64 Pound 30.64
    10072130 40.51 Pound 40.51
    10072170 243.68 Pound 243.68
    10072175 216.76 Kg 385.40
    10072175 73.65 Kg 0.00
    10072175 209.74 Pound 0.00

  16. i want to search out particular employees detail from the bulk employee list record....it is difficulty to find out manually search regarding the employees ...that's why plz send me the logical formula how it iz possible to find out through excel easily ......

  17. Pricing Table Dilivery Table
    No of Units Price/Unit Method R/Unit
    1 70 Air 3
    10 60 Rail 2.5
    100 50 Road 2
    200 40 Ship 1
    500 30 Truck 1.5
    1000 20

    Sales Table
    Deliver Method Units Sold Total Cost Unit Delivery
    Air 25
    Air 260
    Rail 12
    Rail 125
    Road 150
    Road 230
    Ship 2
    Ship 679
    Truck 580
    Truck 1010

    Can You Please Assist me To Answer this Question

    "The Pricing Table is placed in Columns A and B respectively while the Delivery Table is placed in Columns D and E of the Excel sheet. The Sales Table has 7 columns in Columns A, B, C, D, E, F and G respectively.

    Exercise 1
    What will be the “Total Cost” of products purchased for each delivery method, having been given the number of Units Sold in column B of the Sales Table? Using the LOOKUP Function tool in Excel 2013/365, write a formula in the TOTAL COST column (C12:C21) on the Sales Table to determine the Total Cost of products purchased in Rand. Thank you

    1. Hi

      was this question answered?

  18. Hi

    Please help me I have a spreadsheet with total columns for each customer, I need to put totals for each customer at the bottom and the totals must be the total paid invoices that I have formulated or for paid invoices. as I put invoice the total must appear

  19. Pls help me.
    there four colums
    Date party Bags Status
    02-Jan Raja 15 Lifted
    02-Jan Prem 05 Lifted
    03-Jan Raja 105 Process

    Now i want a formula that how many bags lifted on 02-Jan ?/

  20. HI

    i have used if function and all the results of that function is a number, at the end i want to make the sum of all these results how to make that as the sum function wont make it as the result is not shown as a number

    thanks

  21. I have 20 codes in sheet 1 and need to sum up the total sum given on each codes from sheet 2. Can you help me come up the formula?

    Ex.

    Sheet 1:
    CODE - TOTAL QTY
    ABC1 - 2212
    ABC2 - 271
    ABC3 - 486
    ABC4 - 111

    Sheet 2:

    Store1:
    Code - Size Total Qty
    ABC1 - 50in 500
    ABC1 - 20in 604
    ABC1 - 45in 332
    ABC2- 64in 122
    ABC2- 220in 123
    ABC3- 123in 443
    ABC4- 14in 443
    ABC4- 122in 111

    Store2:
    Code - Size Total Qty
    ABC1 - 50in 222
    ABC1 - 20in 223
    ABC1 - 45in 331
    ABC2- 64in 15
    ABC2- 220in 11
    ABC3- 123in 43

    Appreciate your advise for this formula

  22. Hi Svetlana,
    Could you please help me with my table. I have the below database with more than 680 rows of information. There are positive and negative values in the column, the total sum of which is zero. How do I build the formula to exclude all these matching values from the column?
    Many thanks in advance for your help!
    Kind regards

    1. Hi, Viorica,
      what do you understand by "matching values"? Those that are completely identical? Or maybe those of the same number but with different signs (positives/negatives)?
      For now we can assume that if you have 0 as a result, it means that every positive value has a corresponding negative value. If you need to sum only positive ones, then:
      =SUMIF(A1:J12, ">0")
      For negatives only
      =SUMIF(A1:J12, "<0")
      If this won't solve your task, let us know more details.

  23. Hello sir,
    Could you help me I want to make my table. When I use SUMIF it just Sum for whole column only. I want to sum at concern code no. row and column. How can I sum code 100 in total by using SUMif or vlookup or hlookup or arry.
    Code Job 1 job 2 job 3
    100 $30
    101 $50
    103 $15
    101 20
    200 $10
    103 $11

    Best regards,
    Ying Kham

  24. Hello Svetlana,

    I couldn't follow ur guide, could you pls help me guide to write one formula, forexample.

    All my data in sheet2 and on sheet1 i have summary table that for specific ID of person i want summ all data from sheet2. how to do?

  25. Hello,
    I have a spreadsheet with different tabs. I want to figure out how to count how many sales of a particular product (9 products), was sold by each salesperson (150 sales people. In addition I want to figure out how much $ those sales produced by each salesperson. Theis data is housed on a tab called March 3 Data.
    Please help.
    Thank you
    D

  26. Hi.
    I have an excel file with 30 tabs each with a range of financial data covering a 1yr period. I would like to, on a separate tab within the same file, create a formula which searches for the MAX and MIN value for each of those ranges of data, referencing (I suppose) two date cells which represent the range I am looking to search within. In other words, today, I would like to search for the MAX value during the last month on all of those tabs. And then, in a week, I would like to be able to adjust the search to again find the updated MAX value for the last month (ie, with the last data point being one week later). Can I do this? If so, how? Thanks, Kim

  27. Hi,

    Have a question regarding sum + vlookup. In your example, you made the col_index_num an array by using {2,3,4,5...}. Is there a way to make this dynamic by referencing a value in a cell? For example, I input a value into cell V1=2 and I would like it to sum over 12 columns i.e. V1 to V1+12. The array wont let me do {V1:V1+12}. Any help is appreciated.

    Thanks
    Praveen

  28. across rows in the same column

  29. I am trying to something very simple and cannot figure out how to do it by reading your examples. Let me explain. I have two spreadsheets where I am looking up the value in one an comparing it to the value in the other and where there is a match, returning the value in the corresponding column number =iferror(vlookup(B6,DetailDate!$D$59292:$BQ$59291,31,),0)
    This returns the first value of the row that matches. The problem is that there are multiple rows that match and I need to sum them together before returning the value. What would the formula be?

    1. I tried to create an array for the one column (top to bottom) but it returns a 0 value.

    2. I need to add the values in the same column but in multiple rows.

    3. The example sum(vlookup)you provided adds values across columns. I need to add them across rows.

  30. Beautifully written and clear guide, thanks Svetlana!

  31. I need some assistance with sumarising some detail information from the following sheet:

    Category; Trx Date; VOLUME

    In this sheet I have multiple categories that gets repeated on numerous Trx Dates.

    I need to sumarise this information into a sheet that looks like this:
    Trx Date 1 Trx Date2 Trx Date3
    Category 1
    Category 2
    Category 3

    Can someone please help me with the formula to achieve this.

    Thanks

  32. I HAVE 2 SPREADSHEET AND I WANT INVOICE NO. FROM 2ND SHEET IN 1ST SHEET BUT CONDITION IS THAT ALL PRODUCT CODE DETAILS MATCH WITH 2ND SHEET PRODUCT CODE PLEASE HELP ME WHAT FORMULA I USE.

  33. I have a spreadsheet that adds up daily usage of a material and subracts from the balance. It also adds that incoming inventory back in.
    Example =(K3781+I3782)-(J3782)
    Currently I manully enter the projected usage into this cell, this then gives me the projected balance.
    Example =(K3781+I3782)-(J3782)-940-568
    I would like to be able to export the information into another tab and excel calculate for me. The problem that I have is that multiple usage on the same date. I dont want to sum because I would like to associate the usage to a partiacular number. I can also have the same number on the same date that drives usage. Would it be possible submit an eample of what I am trying to explain?

  34. hi,
    am looking for a formula where I can average 7 values from 8 by taking the first 5 and choosing the best 2 from the remaining 3 values.

  35. hi
    I am getting close to the formula
    i want to enter a serial number in Column B1:B23 page 1
    then it matches serial number page 2 column A:1:a23
    than data(written and date) from page 2 column b,c shows up in page 1 column c,d
    i cant get it without error and both columns showing up

  36. Hi, So I have a column A of name of people and their spending in a year in Column. I need to calculate how much each person spent by looking up the name so that if new entries are added the SUM automatically gets updated. Is their a way?

  37. So close. The solution I used was SUMIFS which is similar but uses two columns to grab info from a third.

    Thanks.

  38. Does the Vlookup and Sum work with repeated values? for example if you had 2 columns with the name apples and want to sum both of the totals for both of the apple columns into one combined total. I am trying to generate a spreadsheet that will wind repeating cost codes and add the repeating cost codes for me. Please let me know how i can go about this!

  39. Hi

    As part of a larger problem I want to calculate the sum of top "n" values, where n will be user defined.

    The sum will be calculated for an array,which has been calculated using several conditions using array formulas.

    Eg: After all the conditional array formula, say I am getting an array as {0;23;45;0;0;10;1}
    This array is dynamic based on the conditions provided. So, is there a way to find the sum of say first 3 or first 4 non zero numbers from this arrray ?

    Thanks in advance

  40. Thanks for the great article and I'm hope I'm not repeating a previous question...

    When writing in the numbers of your array {2, 3, 4, 5, 6} and so on, is there a way to list an entire range of values at once? Say if you want to sum indexes 2 to 100. I noticed you mentioned the index function if I'm trying to sum over hundreds of columns for a given criteria. Should I not be using this array vlookup option for this case?

    Happy to investigate another link if the answer is already out there. Pass it my way.

    Thanks again for your clear explanations!

    Ed

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

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

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

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

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

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

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

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

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

  50. i have tried this with sumif(vlook up) but i dont get the esult and i cudnt find my error in 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 :)