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

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

    Regards

    Tlhogi

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  20. helo,
    please I need your help.

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

    Appreciate your support

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

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

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

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

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

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

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

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

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

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

  31. Hi Svetlana,
    I have those simple data in excel. How can I sum all the number right below letter A, or B or C if the staggering on in order in Excel. Thanks.

    A B C
    15 18 50

    B C A
    13 19 23

    C A B
    30 15 20

  32. hi
    I have a expense chart in which I2 to I25 have list of categories.
    A is for seriel number
    B is for date
    C is for amount of expense
    D for category
    E is for mode of payment
    F is for expense category
    G is for commulative total

    Now I want to make a separate sheet which can show all the list of categories and after each category there should be total of expenses done in that category.

    Kindly help me how to do. I wantto make separate sheet for this.

    Regards
    Gk

  33. Hello all,
    kindly help
    i am making result card of students in ms excel.
    i need to add marks obtained and total marks
    but
    there is a restriction
    there are total 7 subjects with different total marks e.g.
    English (100), History (100), Mathematics (85), Chemistry (85), Physics (75), Computer (75) and Biology (85).

    Rest of subjects are compulsory for students except Biology and Computer
    Those who attempt computer will not be attempting Biology and vice versa

    so i need a formula to get total marks sum

    example what i want
    if ( bio == 0 )
    {
    sum English+History+Mathematics+Chemistry+Physics+Computer
    }
    %answer is 520 %

    else if (comp == 0)
    {
    sum English+History+Mathematics+Chemistry+Physics+Biology
    }
    %answer is 530 %

    %In case some is absent from computer or biology paper we need to add total of their respective subjects so %

    else if ( Comp == 'A' )
    {
    sum English+History+Mathematics+Chemistry+Physics+Computer
    }

    else if (Bio == 'A')
    {
    sum English+History+Mathematics+Chemistry+Physics+Biology
    }

    waiting for a favourable response

    1. Hello, Yousaf,

      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.

  34. If I have a table with the following:

    FP2000 30,000
    FP2001 45,000
    FP2000 50,000

    What formula would I use to grab the repeating "FP's" and then sum them up, without using a pivot table?

  35. Hello , i would like to know which formula to use if i have a colum of values and i want to point out the values that would add to a specific number , for example ;
    D
    1
    2
    4
    8
    23
    19
    7

    And out of that range i would do a formula that would tell me which will add up to 29.

  36. Am trying to sum column m where last 6 characters in column a is in month of dec-15, but I only seem to be summing the first occurrance of dec-15 in column a. Any ideas? Thanks for looking.

    =SUMIFS(Fees!M2:M110,Fees!A2:A110,RIGHT(TEXT(A58,"mmm-yy"),6))

    1. Hello, Steve,

      Please try this array formula:
      =SUM((M1:M110) *(RIGHT(TEXT(A1:A110, "mmm-yy"), 6)="Dec-15"))

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

  37. Can the VLOOKUP function be used in conjunction with the SUM function to replace the sum range function argument?

    1. Hello, Gary,

      To be able to assist you we need to see your data. Please give us an example.

  38. s-003 $10,450 Sales person: Dan Brown
    s-003 $2,001 Sales: $13,349
    s-005 $1,900
    s-008 $7,832
    s-005 $193
    s-008 $1,500
    s-008 $3,900
    s-004 $346
    s-004 $263
    s-004 $344
    s-008 $117

    same type but different excell

  39. Hai,

    Please send how to calculate excel to excel sumif+vlookup

    total qty required in one format

  40. Svetlana

    From your about example, {=SUM(VLOOKUP(B2, 'Monthly sales'!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}, is there anyway to replace the hard coded set {2,3,4,5,6,7,8,9,10,11,12,13} with a cell reference or set of cell reference like {A6,B6,C6,D6}? I would like to not have to edit the set reference every time I change or add a new section to my workbook.

    Thanks for the advice!!

  41. I have a table of 3 columns: Course, Credit and Category:
    Course Credits Category
    10 3 GE
    20 3 GE
    22 3 GE
    40 3 GE
    56 3 Surveys
    71 3 Lower Division CW

    I have another worksheet where I am entering the courses per instructor and totaling the units

    so for Fall 15 semester teacher teaches 3 courses and it totals the units
    Cindy 22 40 56 9 units
    Bill 71 56 40 9 units

    I used a vlookup to calculate the units based on which course was entered and total it by semester by instructor. That works fine.

    I want to now total the categories for each semester

    Categories
    GE
    Surveys
    Lower Division

    It should look up the category associated with the course (Above table course 10 is a GE Category) and count the instances where it finds a course that falls into the GE Category.

    GE 15 courses
    Surveys 10 courses
    Lower Division 9 courses

    Any help you can give me will greatly appreciated

  42. Dear i have following example data of 3 different clients. Now if i want to sum "Apple", "Orange" etc. then what kind of formula should be use. plz guide.
    Ahmed & CO =
    APPLE 5 2 3 4
    ORANGE 4 1 2 3
    LEMON 3 4 5 5
    COCONUT 2 1 3 4
    Sharif & CO =
    ORANGE 4 2 4 2
    APPLE 6 4 4 5
    COCONUT 3 7 4 6
    Sharif & CO =
    COCONUT 2 4 3 2
    ORANGE 4 3 4 2
    APPLE 5 3 2 4

  43. Sl No. ItemID Program Category Efforts
    1 P001 Interior Design 120
    2 P002 Exterior Drawing 40
    3 P003 CAE annotation 33
    4 P004 Seating Packaging 456
    5 P005 Interior Design 22
    6 P006 Interior Drawing 55
    7 P007 Exterior annotation 6677
    8 P008 Exterior Packaging 244
    9 P009 Exterior annotation 786
    10 P010 CAE Packaging 44
    11 P011 CAE Design 245
    12 P012 Seating Packaging 9887
    13 P013 Exterior Design 54
    14 P014 Exterior Drawing 444

    Hi Svetlana,

    Please help me to get a formula to get the total efforts when program and category in different rows same.
    Exampple : from above data
    Sl No. 4 & 12 have same program and category, so their sum is 10343.

  44. Hi

    I have an expense report that runs chronologically for an entire year. I would like to have a formula that can sum the amounts by month and by item. I.e. Sum all amounts in column X (highlight entire range) that say January in column Y and "Elect. bill" in column Z. Is this even possible?

  45. I have 50000 entries in excel sheet and i have to do page wise totals. Needs to print on dot matrix printer later on. I also need page wise summary of totals.
    there are entries like voucher No.s having different transactions. I want to club the total amount against the one voucher No. instead of repeating it. it will save paper. Please help

    Thanks

  46. Very good site , very educative

  47. I have a situation similar to the first example on this page, i.e. fruits in multiple rows and months as columns. The formula is great for finding one fruit and adding multiple columns in the array. However, I have the same fruit listed in multiple rows. I need to add the months amounts for each row that the fruit is listed. Is there a way I can do this in a formula without creating a pivot table?
    Thanks

  48. I need a formula to update a sales sheet. as i have multiple of contract numbers in one column and each contract has some value that i want to sum. with pivot table it be done however i need a formula. can please advice...

    Contract # Status Car Plate # Pymt_Customer
    577 Open 7823 1500
    586 Open 7827 0
    586 Closed 7827 165
    584 Open 7822 1500
    584 Closed 7822 -1020
    577 Closed 7823 -265

  49. i need to add values corresponding to (say) 4th coloumn if values or data in 1st 2nd and 3rd coloumn match.

    division name date expense
    sales a 20-Jul-15 1
    pdtn b 5-Jul-15 2
    sales c 10-Jul-15 3
    pdtn d 20-Jul-15 4
    sales a 5-Jul-15 5
    sales a 10-Jul-15 6
    sales a 20-Jul-15 7
    sales c 10-Jul-15 8
    sales c 5-Jul-15 9
    pdtn d 20-Jul-15 10

    if data of A , B AND C coloumn match then it should do sum total of values in D
    for any new unique combination of A,B and C coloumn a new row automatically should be created

    I want the result to be like this

    sales a 20-Jul-15 8 (1+7)
    pdtn b 5-Jul-15 2
    sales c 10-Jul-15 11 (3+8)
    pdtn d 20-Jul-15 14 (10+4)
    sales a 5-Jul 5
    sales a 10-Jul 6
    sales c 5-Jul-15 9

    can someone help me on this. thanks in advance

  50. Hi, Svetlana. I read your article on SUM and VLOOKUP and I practiced some examples about the combination of SUM and VLOOKUP/ INDEX. And I have some questions relating to the first example in above article.

    I want to extract the apples' sales figures for 3 months (Jan, Feb, Mar) separately in 3 cells. I choose 3 cells and enter the same array formula:

    {=VLOOKUP("apples",'Monthly sales'!$A$2:$M$9, MATCH({"Jan","Feb","Mar"},'Monthly sales'!$A$1:$M$1,0))}

    Result of above formula is 3 cells contain sales figures of 3 months.
    And I use INDEX formula and receive the same results:

    {=INDEX('Monthly sales'!$A$2:$M$9, MATCH("apples",'Monthly sales'! $A$2:$A$9,0),MATCH({"Jan","Feb","Mar"},'Monthly sales'!$A$1:$M$1,0))}

    My PROBLEM is:
    1- When I use the combination VLOOKUP with SUM and received a correct result as the following:
    {=SUM(VLOOKUP("apples",'Monthly sales'!$A$2:$M$9, MATCH({"Jan","Feb","Mar"},'Monthly sales'!$A$1:$M$1,0)))} =$26,189

    2- But when I try to combine SUM with INDEX in a similar way, I only receive the apples'sales of Jan:

    {=SUM(INDEX('Monthly sales'!$A$2:$M$9, MATCH("apples",'Monthly sales'! $A$2:$A$9,0),MATCH({"Jan","Feb","Mar"},'Monthly sales'!$A$1:$M$1,0)))} =$2,773

    - However, when I calculate the apples' sales for all 12 months by using SUM and INDEX (with col_num =0), I receive the correct result.

    {=SUM(INDEX('Monthly sales'!$A$2:$M$9, MATCH("apples",'Monthly sales'! $A$2:$A$9,0),0))} =$172,008

    Could you please explain for me why the second formula not return the correct result?
    I looking forward to receiving your reply. Thank you.

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