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 2. Total comments: 200

  1. I know this thread is old but just shooting it out there... I have a multi sheet spread sheet keeping track of job hours. I have used VLOOKUP in succession to sum all the hours on multiple sheets and it works great... Until it gets to a sheet that does not contain the lookup value. I have searched all over for my issue, and VLOOKUP may be the incorrect solution. I was wondering if I could rattle anyone's brain to make this work. I.E. I have 1 excel document with 52 tabs. Each tab is a work week starting from January so WW1 is all the hours on sed jobs I did for that week. "joes house 2 hours ; mikes house 3 hours" etc... WW2, WW3 etc... Until WW52. This is the function I made to add hours together... =SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE)) And it works great. But when that job is finished it is not on (for example WW32 tab). Hence I get the #N/A error. so for example, as the previous one works great when I expand the formula to cover all 52 sheets... =SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE)) I get the #N/A error because the job is not listed on WW32. But I may add hours to that on WW45. Is there a way to make VLOOKUP skip a sheet that does not have the referenced value and continue summing it till the end? I apologize, this may be as clear as mud but I will clarify anything if need be...

      1. Alex, thank you,

        I have read that article before, but it does not seem to cover the summing part of my equation. you can set iferror to return text or even blanks, but the article does not cover summing. I'm looking for how to SUM multiple sheets when some of the sheets do not contain the lookup value. Instead of #N/A it just returns "you've ENTERERED TOO MANY ARGUMENTS FOR THIS FUNCTION"...

        =IFERROR(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE),"")

        And that's just 3 sheets

        1. Hello!
          If you want to sum values, use the SUM function. Apply the IFERROR function to each VLOOKUP. In the value_if_error argument, use not the empty value "", but 0.
          For example,

          =SUM(IFERROR(VLOOKUP(O30,'WW29'!$A$7:$M$110,13,FALSE),0), IFERROR(VLOOKUP(O30,'WW30'!$A$7:$M$110,13,FALSE),0))

          I hope I answered your question.

          1. You sir, are a genius! I cannot thank you enough! That works perfectly! I really appreciate your time in helping me with this calculation!

  2. What if I want to add up cells that are not a numeric value? Like I have a list of names and I want to add all the "Amy" cells up to tell me how many there are?

  3. Hi,
    sumproduct works great if want to sum all values
    But I want to sum only positive or only negative value

    eg.
    A 1
    B 2
    C -5
    A 2
    C 6
    B 4
    B -3

    now I want
    positive sum of A (Result = 3)
    negative sum of A (Result = 0)
    positive sum of B (Result = 6)
    negative sum of B (Result = -3)

    Thanks

  4. Im trying to find and sum a specific phrase corresponding with numbers. 2 separate columns - one with the phrases and one with numbers.

    Then if possible to add them all up in entire workbook.

      1. Thank you! It works!

  5. Hello,

    I'm trying to create a formula on sheet 2 that pulls data from Sheet 1.

    Column F has priority numbers 1-3. and column V either has a blank or a date in the cell.
    I need to create a formula to sum up how many of the Priority 1's in (column F) have a date (in column V).

      1. Cell :
        A1 alpha B1 1000
        A2 beta B2 2000
        A3 gamma B3 3000
        A4 alpha B4 4000
        A5 beta B5 5000
        A6 gamma B6 6000

        I need the function to find all the alpha and sum their corresponding numbers

  6. I am trying to pull the sum of multiple cells in the same column that have the same part# but different qty's.
    part A 6
    Part B Qty 2
    part C Qty 1
    part A Qty 4
    part A Qty 9
    Part C Qty 20

    1. Hello!

      I have a sheet that has some company names in column V and a running tally in column M. There are cases where the same company name comes up more than once, in which case I want to either SUM / find the MAX value of all the cells in column M that correspond to that company name. My goal is to count the number of companies that have a zero on the running tally across all instances of that company name in the sheet, so I need to exclude any that have a 1 or more in the running tally across all instances of the company name (hence the SUM / MAX). Unfortunately, I cannot edit the layout of the sheet and my final count has to be in a single cell (I cannot generate other tables / reference worksheets), and I have been unable to figure out how to make this work. Any help or insight you could provide would be greatly appreciated!

  7. Hi. I have a list of grades in a row
    A* A B C A C E B C A*
    and a vlookup
    A* 60
    A 50
    B 40
    C 30
    D 20
    E 10
    I would like to find the average of the row grades, and I also need to ignore any blank grade cells. This has been driving me crazy as I am sure there is a very simple solution. TIA

    1. Hi Alex. Thanks for your quper quick reply.
      I would never have worked that out! Still having a few issues though. I have reversed the order of the grades as follows:
      {"E","D","C","B","A","A*"} as assume it works on the position of the value and as E is worth 10, then it should be first?
      Also, it seems that A* and A are giving a value of 5, instead of 5 and 6. Is it because of the wildcard?
      And lastly, I should have mentioned I need a U grade to give a value of 0. Is there a way to incorporate this?
      Thanks

      1. Hi,
        You are right, you need to use the reverse order of characters.
        If you use a wildcard character, then the calculations will be incorrect. Replace it with a different character.
        If there are blank cells in your data, they should be evaluated as 0. The AVERAGE function does not work with text and blank cells. Therefore, the score U=0 will be equal to an empty cell.

        1. Thank you.
          I don't think I will be able to use this formula. The A* is a grade, so to replace the * with a different character would cause problems elsewhere. Also, U is a grade with 0 value but needs to be used to calculate the average.
          I don't suppose there is another way?

          1. Hello!
            I believe the following formula will help you solve your task:

            =SUM(IFERROR(VLOOKUP(A1:I1,{"A*",60;"A",50;"B",40;"C",30;"D",20;"E",10;"U",0},2,0),0))/SUM(--NOT(ISBLANK(A1:I1)))

            Hope this is what you need.

            1. :( So sorry, it's not working. The data I have is (12 grades and 1 blank between A* & B ):
              B A* C A A A B A* B A A A
              and the formula I have is:
              =SUM(IFERROR(VLOOKUP(J4:V4,{"A*",60;"A",50;"B",40;"C",30;"D",20;"E",10;"U",0},2,0),0))/SUM(--NOT(ISBLANK(J4:V4)))
              and the answer I get is:
              3.333
              When I evaluate the formula,
              =SUM(IFERROR(VLOOKUP(B,{"A*",60;"A",50;"B",40;"C",30;"D",20;"E",10;"U",0},2,0),0))/SUM(--NOT(ISBLANK(J4:V4)))
              then
              =SUM(IFERROR(40,0))/SUM(--NOT(ISBLANK(J4:V4)))
              then 40\12
              I really appreciate your help with this. I have managed to 'solve/get round it' by using Vlookup on each individual grade, using interim cells to hold their value, then averaging them, but it's a bit messy.

              1. Good morning. I think I am going to have to give up on this as I must be doing something wrong. (I have even copied your formula into a new spreadsheet with A1->L1 containing the grades and I still get an answer of 3.33. It seems to only pick up the first grade, B, convert to a value and then divide that by 12 rather than look at all the grades in the range)
                Thank you for all your time and help, much appreciated, Michele

              2. Hi!
                I'm sorry, but the formula is not 3.333, but 47.5. This is the correct result. The formula is working correctly. Sum of ratings 570. 570/12 = 47.5 Check it out.

    2. Hello!
      If I understand your task correctly, the following formula should work for you:

      =AVERAGE(IFERROR(MATCH(A1:I1,{"A*","A","B","C","D","E"},0),0)*10)

      or if you need to ignore empty cells -

      =SUM(IFERROR(MATCH(A1:I1,{"A*","A","B","C","D","E"},0),0)*10) / SUM(--((IFERROR(MATCH(A1:I1,{"A*","A","B","C","D","E"},0),0)*10)>0))

      I hope I answered your question. If something is still unclear, please feel free to ask.

  8. Pls help to construct a formula for below scenario

    we buy goods from many suppliers on a daily basis , we have assign unique no for each supplier , we type their details in a sheet , with their id and their quantity on a daily basis , next day , next column ,like wise ...

    we want to lookup their details on day order to a another sheet to prepare tabular , how to do that

    if anyone can send their mail id , i can forward the files to you

    1. i have mailed now m kindly check
      , thanks

  9. Name | Flour | Baking Soda | Eggs
    A 2 0.1 5
    B 1 0.2 3
    C 0.5 0.05 1
    A 4 0.5 10

    Input Name: e.g A
    Ingredient: e.g Flour

    Total: ???
    I want to total the ingredient whenever the new name or new ingredient is selected. I have the formula already but it's only for one column and not applicable for other columns. it won't output new total when new ingredient is selected. How do you incorporate other columns?

    Pls help!

      1. Thank you for your response.

        I have to add in this ex below,

        Name | Flour | Baking Soda | Eggs
        A 2 0.1 5
        B 1 0.2 3
        C 0.5 0.05 1
        A 4 0.5 10

        Input Name: e.g A
        Ingredient: e.g Flour

        Total: ??? total flour of A (2+4=6) since A is inputted twice.

        if Baking soda is chosen, how to automatically update the total of Baking soda that A selected? in this case, it should be (0.1+0.5=0.6).another case, if eggs is selected,the total of eggs that A selected will be (5+10=15). I have no idea how to make a formula that will incorporate ingredient inputted (eg.flour). I hope you can understand me. sorry for my english.

        1. Hello!
          I see from your subsequent comment that your task differs from the one you originally described. For each ingredient, the SUMIF formula can be used. For example, to calculate the amount of baking soda for A

          =SUMIF(A2:A200,"A",C2:C200)
          or
          =SUMIF(A2:A200,F2,C2:C200)
          where F2="A"

          If there is anything else I can help you with, please let me know.

          1. Thank you for your reply. I hope you will be patient answering me.

            the formula you gave can only sum one column when ingredient "flour" is selected. it doesn't work if the ingredient is changed the C2:C200 is for column Flour values, how about other columns?

            What I need help from is, when I want to choose "baking soda" instead of flourand it is still for A, a function that will look up every columns not just for "flour" but for every columns. how can you do incorporate it in the formula? that will automatically output (0.1+0.5=0.6).

            1. Please disregard my previous reply.

              Thank you for your reply. I hope you will be patient in answering me.

              the formula you gave can only sum one column when ingredient "baking soda" is selected. it doesn't work if the ingredient is changed. the C2:C200 is for the column of Baking soda values, how about other columns?

              What I need help from is, when I want to choose "flour" instead of baking soda and it is still for A (input name), a function that will look up every column (other ingredients) not just for "baking soda" but for every column. how can you do incorporate it in the formula? that will automatically output (2+4=6).

              1. Hello!
                The formula I gave you is for one ingredient. To use it for another ingredient, you need to change the column reference.

  10. I have all the dates of the month and several different companies with amounts I am looking to make a formula that can add up the amounts by company and month. Every time I try I get 0 or invalid. It's driving me crazy. So based on Jan company ABC brought in XX amount.
    Jan 1 ABC XX
    Jan 3 XYZ XX
    Jan 15 ABC XX

    1. Hello!
      You cannot use the MONTH function as a condition in the SUMIFS function. Please try the following formula:

      =SUMPRODUCT(--(MONTH(A1:A5)=1),--(B1:B5="abc"),C1:C5)

      I hope it’ll be helpful.

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

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

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

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

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

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

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

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

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

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

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

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

  23. Item Code Item Description
    A1 Processors
    A2 Printers
    A3 Motherboards
    This sample is belong to sheet1

    Item Code Item Description
    A1 vlookup formula pls!
    This sample is belong to sheet2

    what is the exact formula for the "item description" column in sheet number2, if I enter A1 on the "item code" column? the Processor should be the output.

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =VLOOKUP(A2,sheet1!$A$2:$B$4,2)

      Hope this will help.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  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. Hi All,

    I have two worksheets.

    One listing all days against a individual contract e.g.

    Contracts Days
    1234 1
    1234 4
    1234 2
    1234 9
    1235 1
    1235 6
    1235 0
    1235 11
    1236 7
    1236 2
    1236 20
    1237 1
    1237 7
    1237 5
    1237 5

    and the other where I want to look this days total up for that contract e.g.

    Contract Total days
    1234 16
    1235 18
    1236 29
    1237 18

    What VLOOKUP & SUM formula would I use as column one would not work as it is SUMing rows.

    1. Maybe,you can try using SUMIF function.

      Supposing that in Sheet1 the column Contract_number is A2:A16 and column Days is B2:B16 ; in Sheet2 column Contract is A2:A5
      In Sheet2 choose ALL cells B2:B5 and enter a same array formula in Formula bar:
      =SUMIF('Sheet1'!A2:A16,=A2:A5,'Sheet2'!B2:B16)

      Due to it is an array formula and should be ended by Ctrl+Shift+Enter

  42. hi.. im having trouble with my excel..
    you see..my proble is like this
    qty.-------u/m-------product
    2 ------rolls------ thermal paper
    5 ------packs------ pandan jelly
    4 ------rolls ----- thermal paper

    i want to have a summary like this
    thermal paper----- 6
    pandan jelly -----5

    1. Hi Mei,

      Supposing that Qty. is column A and Product is column C, you can use the following SUMIF formula:

      =SUMIF(C2:C100, "thermal paper", A2:A100)

      1. thanks!! that helps a lot!!
        but there is another one.
        example:
        qty----u/m----product------amount
        2-----packs---12 oz lid----
        5-----rolls---thermal paper---
        1-----box-----12 oz lid---

        what i want is somehow, when i type the qty, u/m and product, it will give like...
        thermal paper---6----$
        12oz lid (pack)----2-----$
        12oz lid (box)-----1-----$
        there should be a difference in the price of the per box and per packs... thanks

  43. THANK YOU FOR YOUR WONDER FULL POST. I CAN'T REALLY GET IT RIGHT!!!
    BSERVATION
    FOR FIND SAME MATCH =VLOOKUP(A:A,A:B,2,FALSE) (MIDLE A:B SHEET TO SHEET UR O)
    FOR SUM OF MATCH =SUMIF(A:A,A,B:B) (MIDLE "A" IS OBSERVATION TO FIND)

    THIS IS SUM OF ALLLL YOUR POST. YOU CAN MAIL ME IF DOUBT.

    1. ADD SUMIF CRITERIA (MIDDLE AREA) BY SELECT THE CELL YOU WANTED TO SUM.

  44. Thanks Svetlana! Another question, We got the below formula to work, Thanks for the Ctrl+Shift Enter tip.

    {=SUM(VLOOKUP("Green",$I$26:$L$28, {2,3,4}, FALSE))}

    Jan Feb March
    Green 10 40 70
    Purple 20 50 80
    Yellow 30 60 90

    120

    However, we could not get the same formula to work for the below table. Help please.

    Jan Feb March
    Green 10 40 70
    Green 20 50 80
    Yellow 30 60 90

    120

    1. Hi Vladamir,

      Both tables seem to be identical. Can you check please?

  45. Hi Svetlana, I copied your formula and my sample data is below. I used the formula, =SUM(VLOOKUP("Aaron",$I$26:$J$28,2,FALSE)) I was expecting 50, but got 30. Help please.

    Aaron 30
    Aaron 20
    Jack 10

    30

    1. Hi Vladamir,

      Since you are working with a single table, you don't need SUM/VLOOKUP. A simpler SUMIF formula works just fine:
      =SUMIF($I$26:$J$28, "aaron", $J$26:$J$28)

      1. Hi,
        Is there any possible way to sum data without mentioning "aaron"

        I mean automatically it will sum if the Column A duplicated

  46. Hello,
    How do I figure out my average sale for a November but for selective days? example: if i want Mondays for all of November?

    Date Sale
    Monday Nov 01, 2014 $150
    Tuesday Nov 01 , 2014 $450
    ect...............

    1. Hello Peter,

      The SUMIFS / SUMIF functions won't do in this case. Try the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM((--MONTH($A$2:$A$100)=11)*(--WEEKDAY($A$2:$A$100)=2)*($B$2:$B$100))

      Where $A$2:$A$100 is the column with dates, $B$2:$B$100 is the sum column. BTW, November, 1 is Wednesday : )

  47. Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 Total
    1 2 3 4 5 6 7 ????
    1 2 3 4 5 6 7 ????
    1 2 3 4 5 6 7 ????
    1 2 3 4 5 6 7 ????
    1 2 3 4 5 6 7 ????
    1 2 3 4 5 6 7 ????
    1 2 3 4 5 6 7 ????

    Hello,
    I need to make a function which will calculate the data till month and will display in Month column. how could i make the function on this?
    for eg, let say, Current month is November, now i want whenever i open the excel document, Excel should auto calculate and sum the data oct and nov month data and display in total column)

    Could anyone help me on this?

    1. Hello Nitij,

      Please specify if you need to sum data for the last 2 months, for the current month or anything else? If the row contains only numbers, you can use the SUM function, e.g. =SUM(A2:G2).

  48. Hello Svetlana,

    I have the following tables:

    Table 1.
    A B C
    Date Name Value
    7/21 Luke 20
    7/21 Kip 18
    7/23 Luke 19
    7/23 Kip 10
    7/25 Eric 8
    7/26 Eric 13

    Table 2 (unique names from table 1)
    A
    Name
    Luke
    Kip
    Eric

    I need to calculate sum of values from table 1 for each name in table 2, but only sum of values that are higher than 10.

    Thank you so much in advance for your help!

    Anya

    1. Hello Anya,

      You can do this using the SUMIFS formula that allows calculating the sum based on multiple conditions. Here is the formula for cell A2 in sheet 2:

      =SUMIFS(Sheet1!$C$2:$C$7, Sheet1!$B$2:$B$7,A2, Sheet1!$C$2:$C$7, ">10")

      It will return the sum of values for Luke higher than 10, in your example it's 39.

      Modify the ranges in the formula according to your real data and copy it across column B in sheet 2. Hopefully this is what you are looking for.

  49. Hello Sayyid,

    This makes the task much easier since you don't need the VLOOKUP function.

    The following SUMIF formula does what you need:
    =SUMIF(B2:B12,"s-003",C2:C12)

    Instead of putting a particular ID in the formula, you can add a cell reference containing it, like this:
    =SUMIF(B2:B12, B2, C2:C12)

    Where B2 is the cell with the ID you want to sum.

    For more info about Excel SUMIF, please check out this article:
    How to use SUMIF in Excel - formula examples

    1. Hello,
      I have data in sheet1 as bill,name,item,qty,p.price(different price will be in same invoice it's because profit diff )
      In sheet 2 to I have bill , name I want to sum the price by bill number how to use it

    2. Hello,
      I have data in sheet as bill,name,item,qty,price
      In sheet to I have bill , name I want to sum the price by bill number how to use it

    3. Thank you very much for your answer

  50. Hello Svetlana,

    I am trying make use of this VLOOKUP AND SUM method in one of my excel sheet to create a summary of the items.
    But I am not able to make the right formula for it.

    Could you please help me with this?

    Example:-

    CODE IN
    101101 420
    101101 362
    101101 83
    101101 129
    101102 42
    101102 53

    Lets say 101101 and 101102 come under a single category.
    How do I look up these specific codes in an excel sheet which has many other codes and add their corresponding values?

    Looking forward to your kindness.

    1. Hello Sahal,

      You can fulfill your task by creating a simple pivot table. Go to INSERT > PivotTable. When the PivotTable pane appears, drag the Code column into the Rows section and the IN column in the Value section.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)