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

  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. I've gone though so many different examples and I cannot find anything that will help me get the result that I'm looking for.

    So I have started a spreadsheet that will have multiple worksheets of data for each school year. Within each school year, I've or will create a named range ($A$6:$C$53). Columns and A & B are the two columns that I need for this issue. Column A is the name of the fund, while Column B is the amount of the fund.

    In addition to a worksheet for each school year, I've created worksheets for each individual fund that allows me to keep a running history of the funds. For most of the individual funds, I've been able to get away with using VLOOKUP as each fund is a unique name. However, the one I'm having issue with is for the "General Funds" as there are several funds that have that name and I'm trying to add them all together.

    On each individual fund's worksheet, the formulas are referencing several different cells A1 is the name of the fund that it is searching for within the named ranges, while A3 and down will be the reference cell on which named range to use.

    For example:
    =VLOOKUP($A$1, INDIRECT($A3&"_Funds"), 2, FALSE)
    $A$1= 'Athletic Funds - The name of the fund to search for
    $A3=FY202122 - The named range to search in.
    This formula returns the value in Column 2 (Column B) corresponding to the fund name from $A$1 from the named range FY202122_Funds.

    However, I cannot get it to sum up the values from Column B when there are multiple funds with the same name. I feel like I've tried nearly everything I can think of...including XLOOKUP, INDIRECT, SUM, SUMIF, SUMPRODUCT, ETC.

    Some of the errors that I've received is #VALUE and #SPILL, and even the most lately, I've gotten $0.00. This is the latest formula that I've used...=SUM(LOOKUP($A$1,A3&"_Funds",FALSE)) and have gotten the $0.00 result.

    Thank you for your time in advance, as this is getting frustrating.

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

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

  5. I need one Calculation Formula...

    I have accounting Ledger in Excel Format for Home Accounting Only with Pivot Table.

    In summary I need the sum of amount of money debited from the accounts of Various Bank accounts...

    Following is the Ledger I created...

    Where I need to know the amount of money debited and Credited from Cash, SB Account, Airtel App, GPay, IRCTC App from the below Pivot Table. The value should be under the Credit and Debit Row seperately.

    MY HOME ACCOUNTS
    SL No Particulars Credit Debit Balance
    1 CASH ₹ 7,195.00 ₹ 7,195.00
    2 SB Account ₹ 74,261.22 ₹ 74,261.22
    3 Airtel App ₹ 500.00 ₹ 500.00
    4 Gpay ₹ 498.00 ₹ 498.00
    5 IRCTC App ₹ 0.00 ₹ 0.00
    TOTAL ₹ 82,454.22 ₹ 0.00 ₹ 82,454.22

    Sl No Day Date Particulars Credit Transaction Amount
    1 Tuesday 01-03-2022 Current Balance Credit SBI Account ₹ 849.22
    2 Tuesday 01-03-2022 Current Balance Credit CASH ₹ 40.00
    3 Tuesday 01-03-2022 Current Balance Credit SBI Account ₹ 1,000.00
    4 Tuesday 01-03-2022 Salary Credit SBI Account ₹ 56,270.00
    5 Tuesday 01-03-2022 Offerings Debit CASH ₹ 5,627.00
    6 Tuesday 01-03-2022 House Rent Credit SBI Account ₹ 3,600.00
    7 Tuesday 01-03-2022 Vegetable Debit CASH ₹ 138.00
    8 Tuesday 01-03-2022 Groceries Debit SBI Account ₹ 742.00
    9 Tuesday 01-03-2022 Groceries Debit Gpay ₹ 278.00
    10 Tuesday 01-03-2022 Others Credit Gpay ₹ 220.00
    11 Tuesday 01-03-2022 Fuels Credit Airtel App ₹ 500.00
    12 Tuesday 01-03-2022 Vegetable Debit CASH ₹ 10.00
    13 Tuesday 01-03-2022 Car Debit CASH ₹ 100.00
    14 Tuesday 01-03-2022 Fruits Debit CASH ₹ 70.00
    15 Wednesday 02-03-2022 Salary Credit SBI Account ₹ 11,800.00
    16 Wednesday 02-03-2022 Clothes Debit CASH ₹ 270.00
    17 Wednesday 02-03-2022 Bike Debit CASH ₹ 440.00
    18 Wednesday 02-03-2022 Others Debit CASH ₹ 360.00
    19 Wednesday 02-03-2022 Foods Debit CASH ₹ 140.00

    Please Assist me...
    Thank You...

  6. I have a problem very similar to the vlookup with sumif, but slightly different:

    given a table like:

    A 110
    B 120
    C 130
    D 150
    E 160
    F 170
    G 180
    H 200
    I 5000

    and a lookup table:

    A 1
    B 1
    C 1
    D 3
    E 3
    F 3
    G 3
    H 4
    I 4
    J 4
    K 5

    in a single cell I want to add up the numbers from the first table, where they lookup to 2 (for example) from the second table, is this possible? (without adding helper columns)

    Would appreciate any advice

    Dominic

      1. Yea my example didn't make sense, in a single cell I want to add up the numbers from the first table, where they lookup to 3 (without adding helper columns)

        so given 3 we lookup in the second table D, E, F ang G as these lookup to 3 so in a single cell we would sum :

        D 150
        E 160
        F 170
        G 180

        giving a result of 660

        1. Hello!
          If table 1 is in columns A and B and table 2 is in columns C and D, then you can use the formula

          =SUM(INDEX(E1:E9,MATCH(FILTER(A1:A11,B1:B11=3),D1:D9,0)))

          I hope my advice will help you solve your task.

          1. Thank you Alexander, but I think my excel might be old as I get "That function is not valid" for FILTER function? I am using Microsoft Excel Professional Plus 2016, not office 365.. Thanks

              1. thanks again, this is very close but only works for me if I put the array into multiple cells, in a single cell it only sums the first value found, I will look at the link you provided, thank you for trying

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

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

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

      1. Hello!
        Unfortunately, you cannot solve your problem using Excel functions in one cell.
        I'm really sorry, we cannot help you with this issue.

  10. Sir,
    Please how can I apply LOOKUP formulae to Sum total sales of a particular sales person from a large data table of sales representatives all combined together in a sheet and compute my result in the sheet 2.
    Thank you

  11. Hi, I want to sum all non-matching values in a vlookup.
    As an example, I have a list of one hundred locations. I have a table of one thousand locations and their population numbers. I can find the population of the matching one hundred locations, now I want the sum of the remaining 900.
    Thanks for any assistance.

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

  13. Hi....I have tons of data on place finishes (1, 2, 3, NR) in 1st column and then company names in the 2nd column. trying to find success rate for each company, there are multiple jobs in column 1. Is there a way I can vlookup and see what place company A finished 1, 2, 3, NR?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  14. Hi all
    please tell me answer for the below question in ms excel only

    I am trying to get the latest month salary from the salary table, where multiple entry of employee ID in salary table for each month ?

  15. Hello thank you for the examples. Hoping you can assist me in my worksheet.
    Im trying to use the vlookup to create a type of net profit worksheet to work out final profit value based on multiple cells and a dropdown, where the dropdown data corresponds to a percentage of the sale price.
    I.e. If ebay is selected from the drop down then the result will be sale price minus various cells minus the vlookup dropdown cell (being 10%)
    If a different option in the dropdown is selected then the 10% will change to another percentage based on what is in the vlookup data table.
    Would love some assistance thank you so much

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  16. 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. Hello!
      I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
      We'll look into your task and try to help.

      1. i have mailed now m kindly check
        , thanks

  17. I want to combine a vlookup formula with a sum and a match formula.

    I have duplicate codes that I want to add up but the values returned are based on a vlookup formula with a match function

    VLOOKUP($A74;OFFCON!$1:$1048576;MATCH('Overall Report'!AL$2;OFFCON!$1:$1;0);0);0

    This returns only the first of the codes, but I want it to sum all of the values.

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. The VLOOKUP function only returns one value. The SUMIF or SUMIFS function is used to calculate the conditional amount.
      I kindly ask you to have a closer look at the following paragraph of the article above - Excel VLOOKUP and SUM - find the sum of matching values.
      I can give more precise advice if you describe your problem in detail.

      1. Hi Sorry, I will try to be more clear.

        The suggestion you have given me is for an array, and I cannot use a match formula with an array.

        I have date

        Code | Product | John Sales | Mark Sales
        1 | Apples Green | 5 |0|
        2 | Pears Round|0|3|
        1 | Apples Red |0|6|
        2 | Pears Oval|2 | 0|
        1 | Apples Green |8|3|
        1 | Apples Red |9|6|

        I want a formula that can give me the sales for Johns total apples (Code 1), but the column for Johns sales is variable, for example one week Mark could be in column D and the other John is in column D, hence the match formula.

        I was using the formula VLOOKUP(F2;A:D;MATCH(F1;1:1;0);0), but if F2 was code 1 and F1 John, then this was returning only 5 instead of the total apples which is 22.

          1. Sir, I am trying to generate a formula to get the total from a certain column in a spreadsheet based on a word in left most column of same spreadsheet, i.e:

            labor $2.00 2.0 $0.00
            blah $3.00 3.0 $0.00
            no $2.00 3.0 $0.00
            labor $2.00 4.0 $0.00

            I am looking to use a formula to total column C only for the rows that say labor in column A. So the formula would return an amount of 6.0 in the example above. Everything I have tried so far only returns the value for the first instance instead of adding them up.

            Thank you for any assistance.

              1. thanks!

          2. HI Alexander,

            That worked thank you for the help.

            The problem is when I try and translate the formula to different sheets within the same workbook, ie the sales data on one sheet and the solution along with F1 & F2 on another it does not pull the correct values, but when on the same sheet, they work.

            Is there a solution for this?

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

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

  20. 1. CENTRAL AGENCEIS LUCKNOW 09AAQPR2533P1ZV 17/04/2020 T0000001 21709.00 Local 30049014 17580.90 17580.90 6.00 1054.85 6.00 1054.85
    33049910 1710.24 1710.24 9.00 153.92 9.00 153.92
    2. KRISHNA PHARMACY LAKHIMPUR 09CEFPS9998G1ZF 22/04/2020 T0000002 32113.00 Local 30039014 1670.40 1670.40 6.00 100.22 6.00 100.22
    30039015 3047.68 3047.68 2.50 76.19 2.50 76.19
    30049014 17527.85 17527.85 6.00 1051.67 6.00 1051.67
    30049015 1458.00 1458.00 2.50 36.45 2.50 36.45
    33059040 3168.00 3168.00 9.00 285.12 9.00 285.12
    34011941 1815.00 1815.00 9.00 163.35 9.00 163.35
    3. ATUL HOMOEOPATHIC&AYURVEDIC SLUCKNOW 09ACVPA7649C1ZB 23/04/2020 T0000003 19571.00 Local 21069099 605.00 605.00 9.00 54.46 9.00 54.46
    30039014 1959.01 1959.01 6.00 117.54 6.00 117.54
    30039015 109.44 109.44 2.50 2.74 2.50 2.74
    30049011 440.75 440.75 6.00 26.44 6.00 26.44
    30049014 10278.35 10278.35 6.00 616.68 6.00 616.68
    30049015 2448.87 2448.87 2.50 61.22 2.50 61.22
    33049910 805.75 805.75 9.00 72.52 9.00 72.52
    33049930 209.00 209.00 9.00 18.81 9.00 18.81
    33059011 313.20 313.20 9.00 28.19 9.00 28.19
    33059019 165.00 165.00 9.00 14.85 9.00 14.85
    34011941 177.97 177.97 9.00 16.02 9.00 16.02
    I have insert a table, I want a result same table but % tax will be added with same %
    5% 500
    5% 600
    9% 700
    9 800
    Result should be
    5% 1100
    9 1300
    this should be in excel and simple formulas

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail.
      Please specify what you were trying to find, what formula you used?
      What number do you apply 5% to?
      It’ll help me understand it better and find a solution for you.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi,
    In sheet1 I have a fixed list of customer names and particular range of date of delivery.
    In sheet2 I have the similar customer names and drivers names with their delivery dates as well.
    my requirement is, I need to pull the drivers names along with corresponding customer names as per the each date in sheet1.
    Will you help me?

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

  33. I have categories instead of names or IDs. I need each instance of a specific category name added. For example, if I have 3 categories: Gas, Misc and Other. Every instance of each of those has a dollar amount, like so:
    Gas - $1
    Misc - $1
    Other - $1
    Misc - $1
    Gas total would be $1, Misc total would be $2 and Other total would be $1
    I need a list of each Category and the total dollar amount of every instance. I used this formula but it only represents the very first instances amount. Instead of all instances.
    =SUMIF(C14:C100, VLOOKUP($H4$2, Lookup_table, 3, FALSE), A14:A100)

  34. Dear Friends, I Will Be Very Grateful If You Help Me to show Distinct Name in Drop-down List and Total of Corresponding using Vlookup
    Thank You in Advance :)

    A B C
    1 2019.12.02 Ronaldo 50
    2 2019.12.02 Messi 20
    3 2019.12.03 Ronaldo 50
    4 2019.12.03 Messi 22
    5 2019.12.04 Pele 15
    6 2019.12.04 Ronaldo 44

  35. Dear Sir, I will be very grateful if you help me to
    Distinct dropdown list name and correstponding total using vlookup
    A B C
    1 2019.12.02 Ronaldo 50
    2 2019.12.02 Messi 20
    3 2019.12.03 Ronaldo 50
    4 2019.12.03 Messi 22
    5 2019.12.04 Pele 15
    6 2019.12.04 Ronaldo 44

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

  37. Apple 50
    Banana 40
    Apple 50
    Banana 50
    Banana 40
    Apple 50

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

  39. I'm trying to build a summation table totaling tuition spent on a client's grandchildren for the past 10 years. I have an annual sheet detailing the institution and month that tuition was paid for each grandchild, and I tried stringing your SUM/VLOOKUP formula together with a "+", but that blew it up and resulted with just the figures from the first column.

  40. Sir,
    I want to get the sum of specific brand to a specific store. Below illustration, please help:
    Worksheet 1: Table
    Branch Name: Glorietta
    Brand Name MTD2018 MTD2019 %Growth
    RJ Baby Cologne
    RJ Baby Oil
    RJ Baby Powder

    Worksheet2: Contains the datas
    *should i change the branch name, it will only show the sales for the specific store.
    Thanks for the help.
    God Bless and more power

  41. i have a problem were I need to lookup sales made by a store for certain date and for a certain date there are various sales made by the various store and I need to add all sales made by a certain for a certain date and there a various stores eg
    date store

  42. Need to Calculate Above 45 Days Value Total Sum from Next Sheet Row it is possible in formula?

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

    Sheet -1
    S.MAN NET AMT.
    Modern Trade 16344.00
    Modern Trade 8847.00
    Modern Trade -23402.00
    Modern Trade 715.00
    DSE002 Dilli 3574.00
    DSE002 Dilli 18352.00
    DSE002 Dilli 16046.00
    Manoranjan S -16344.00
    Manoranjan S 16361.00
    Manoranjan S 3079.00
    Sub-D 726.00
    Sub-D 1054.00
    Sub-D 7841.00

    Sheet -2
    S.MAN Total sale Total -ve sale
    Modern Trade
    DSE002 Dilli
    Manoranjan S
    Sub-D

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

  45. Hello,
    Please somebody tell me how to create formula in excel for my data. Firstly It needs to find the matching word in that column and need to sum adjacent cell data of matching word.

    Thanks

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

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

  47. sheet 1 has full details of all issuing item for the month want to look up sheet 2 as each item how many times issued particular item as one total
    Sheet 1
    Col A = Item Number( Same item Repeat many days(1001,1001,1002,1001,1003,1001)
    Col B= Qty issued (Repeat Many Days)
    Need Sheet 2
    Col A = Item Number ( 1000,1001,1002,1004, etc.)
    Col B = Subtotal of Qty issued for particular Item
    his it possible in Vlook Up , If So please update formula

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

  49. Dear Sir,

    GST TAX% TAXABLE VAL.
    12% 11733.12
    5% 2095.20
    12% 2304.72
    5% 5238.00

    5% = Total Value (In Single Cell)
    12% = Total Value (In Single Cell)

    How to sum if same types Tax % Value is calculating in multiple cell in excel single cell Tax % Wise

    Please help us.

  50. I have 2 sheets. the first (key and total$) has a list of product numbers in the first column. in column "I" I want to place the calculation that gets the product number from column "A", goes to sheet2, matches the product number in column "B" (there are duplicate product numbers), then grabs the corresponding $'s from column "R" (sheet2). and if there is a duplicate product number on sheet2 it will sum them together. I went through your sumif/vlookup instructions and cant get it to work. all help will be appreciated.

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