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

  1. Hi Mei, we have the same problem! I hope somebody can help!

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

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

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

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

  6. Hi Vivien,

    You can use the following formula:

    =SUMIF($A$2:$A$6, $F2, B$2:B$6)

    Where A is the currency column in your main table, B is data for 01-Jan, and F is the currency colum in your summary table.

  7. Hi,

    I have a huge set of data looks like this:
    A B C
    1 CCY 01-Jan 02-Jan
    2 EUR 1000 500
    3 EUR 150 50000
    4 USD 2000 480
    5 JPY 340000 348212
    6 USD 3000 23934

    How can I sum data into below format without using pivot table?

    01-Jan 02-Jan
    EUR
    USD
    JPY

    Thanks.

    Vivien

  8. I have a column of 16 cells with a list of upgrades depending upon the unit style. I have a table with the 3 different upgrades levels and the corresponding price. I would like to SUM the total amount of the upgrades in one cell. I thought it should be a sum with vlookup but it's not returning a value. Any suggestions?

    When I use this formula it only returns the value from the first cell. It doesn't give me the total from the other cells in the lookup value selection.

    {=SUM(VLOOKUP(C40:C55,$A$34:$B$36,2,FALSE))}

  9. Could use some Excel wisdom. I have tried tweaking examples given, but am still struggling. I need to sum data for each month based on a name using 2 tables:
    --Table 1--
    Jan Feb Mar...
    A 5 8 6
    B 2 6 9
    C 3 5 12

    --Table 2--
    A Bob Smith
    B Nancy Jones
    C Bob Smith

    Based on the name, I need a cell to sum all the values associated with the name for the month. So, in the example above, given "Bob Smith", the cell would return a value of 8 for January. I tried SUM & VLOOKUP and then MATCH & INDEX combos, and cannot make it work. Can someone kindly advise? Thank you.

  10. Thank you!

  11. Hey,
    Please help me how to solve this by using v look up.

    01/01/1991 Andy
    02/03/1991 Josepf
    01/04/1990 John
    05/07/1989 Cathy
    01/12/1990 Ray
    03/08/1990 Edmond
    03/07/1988 Steve
    03/03/1987 Peter

    Want to get the names of the person who born in the year 1990 by using Vlookup.

    Thanks

  12. Hi. I have 16 ranges to test a value and return 16 different values For example, if d2 is between 115 and 120, return 2400. But if d2 is between 110-115, return 2300, and so on.....
    I attempted to next if (or(d2>115, d2<=120), 2400, if(....... but I keep getting the error of too many arguments.

    SO, if there a better way or can I use the look up if I put my ranges in another sheet?

    Thanks so much for the help.

  13. Hii
    Can you pls hlp me out.
    I req to make a database of commodities sent, their actual rate, quantity consuned, selling price, profit etc.
    I am not able to make calculations hoe to use vlookup pls help me out. Its very imp.

    Thanx

  14. Can anyone help me solve this. I am a carpenter and have spreadsheet that will compile a list of materials, lengths, and quantities. I want to organize it from largest to smallest, and (type column, then length column) and consolidate like pieces in same row with quantities summed. Ty in advance Norm
    TYPE LENGTH QUANT
    4 X 12 15 1/2 1
    6 x 6 27 1/2 2
    4 X 12 39 1/2 3
    4 X 12 51 1/2 4
    6 X 12 63 1/2 5
    4 X 12 75 1/2 6
    4 X 12 87 1/2 7
    4 X 8 99 1/2 8
    4 X 12 15 1/2 9
    6 X 4 27 1/2 10
    4 X 12 39 1/2 11
    4 X 6 51 1/2 12
    4 X 12 63 1/2 13
    4 X 12 75 1/2 14
    4 X 10 87 1/2 15
    4 X 12 99 1/2 16
    4 X 12 15 1/2 17
    4 X 12 27 1/2 18
    6 X 14 39 1/2 19
    4 X 12 51 1/2 20
    4 X 12 63 1/2 21
    4 X 12 75 1/2 22
    4 X 6 87 1/2 23
    4 X 12 99 1/2 24

  15. It was really that easy, and right after I posted this. I figured it out. Thanks!

  16. Hi..I am stuck..this seems so simple but I can't figure it out. I have a main table with a product ID and a cell for freight. Each month I run a report and one particular product ID might have mulitple entries (vertically in the sheet) with a specific freight charge, I need to create a formula to look at the product ID # in my main table and in that one cell next to it total the freight associated with that product ID# from the report I run each month. for example:
    A1 A2
    Product ID Freight
    1 Formula returns freight total
    2
    3
    4
    5
    6

    Lookup Table
    Product ID# Freight
    1 $2.50
    1 $3.50
    5 $5.00
    5 $5.00
    1 $5.00
    6 $2.50

    So for Product ID# 1 - I need the formula to return $11.00 in the one cell for that product ID# in the main table. For some reason I just can't get it to return even a number.

    Thanks for your help

  17. Hi Svetlana,

    Thank you for nice tip. I used SUMPRODUCT to avoid using curved brackets.
    My question is about array {2;3} , why I cannot use variables instead of numbers? Like {A1;A2}, function returns error in this case.
    Thank you

  18. plz can some one tell me i have two columns one contains serial numbers and other sum random values. i want to make a formula that if a user enters a serial number the sum of random number corresponding to it and all the numbers above it should be added and displayed in a cell. the input can be varied. plz can in need of help

  19. Hello, I have a vlookup formula that is working but i want to add a sum to it to not just bring in one value but the sum of those that match. i've been reading lots of stuff online about sumif, sumproduct, vlookup, index, match and i'm so confused. Perhaps mine is difficult because i need the transpose?
    First Tab
    Cost Center Totals
    1
    2
    3
    4

    Second Tab
    Cost Centers Totals
    1
    1
    2
    2
    2
    2
    3
    3

  20. hi,
    Can anyone give the the guidance for the SUMIF formula, i have put SUMIF formula link from other workbook. after save and closing the work sheet. i tried to open the master file. the link was not updated and showing "#VALUE!"

    If i again open the supporting work sheet, then the link was showing the correct value.

    How to fix it the issue?

    i have pur the following formula
    =SUMIF('[Sheet1.xls]SUMMARY'!$D$4:$E$33,A347,'[Sheet1.xls]SUMMARY'!$E$4:$E$32)

  21. Hi Guys,
    You People are doing a great job, I like the way you are helping people and sorting out their problems individually, this forced me to share my problem with you people.

    I made a workbook consisting of 30 sheets for gas station, i want to keep daily record of that, so i need to know how can i make a final sheet and link those 30 sheets together so they could give me total sales and total expenses of 30 days in the Final sheet ?

    Thanks in advance, Looking forward for your help.

  22. I have two files of data. One file contains outstanding loans, only one loan for each customer. The other file contains all the customer loans paid ( a customer could have numerous loans paid). The customer can only have one loan outstanding. I am trying to make sure that the customer didn't have two loans outstanding at the time. To make sure they didn't have an outstanding from one of the paid loans when it was outstanding. Can anyone please Help?

  23. Hi I have a question. i have 2 excel 2010 sheets. the one sheet collects all the data. the second sheet i want the data from the second sheet to be carried over, but this is where i have a problem the data sheet 1, when the data has transfered to the second sheet i want it to close the sheet 1 off so that i can process a new months work on it. the data from the first sheet will have to look for a employee no and his specific salary amount and carry that to a 12 month report and place it in that month. when this is done i want to close of the sheet so that the data in sheet 2 does not change when i clear the sheet 1 (like a roll over clearing the one sheet but keeps the record of it on sheet 2. what do you suggest i use and do i need a vbs and macros for this.

  24. I have an inventory table that I would like to create a summary page for. Consisting of:

    Column A Column B Column C
    Qty Rec'd Part Number Total Rec'd

    There are several rows of the same part number containing different qty's rec'd in each. I would like to be able to have a formula that would search the whole (same) worksheet for that part number and total the qty's in Column C.

    Thank you in advance for your help ☺

  25. hello!
    i want to use add the values for the particular item appearing several times in a table. how is ti possible. see table below. for example A was produced two times. i want to use count function to know how many times A was porduced and then add the quantity for A is one location.

    element Prdouced
    A 5
    B 3
    X 5
    X 15
    B 11
    A 20

  26. HI SVETLANA
    I have budgeted data in one sheet having Budgeted amount of all account cods and actual data having a lot number of same codes in other sheet, I want to link other sheet with budgeted data and Want to sum up all the amounts of same codes in second sheet actual data and show parallel in next column of budget sheet. Can you please advise how can I do?

    For Example
    Sheet One Budget Data
    Account # Budget Amount Actual Amount

    1 50000 sum of account#1 from second sheet (actual data )
    2 15000 sum of account#2 from second sheet (actual data )
    3 30000 sum of account#3 from second sheet (actual data )

    Second sheet actual data
    Account # Budget Amount
    1 5000
    2 2000
    1 6000
    1 3000
    3 4000
    2 5000
    1 2000
    3 3000
    2 7000

    regards

    Khan Afzal

  27. Hi Svetlana Cheusheva and thanks for the extremely helpful tutorial. Referring to the first table above, I'd like to know whether we can have a formula that tells us the average sales for a particular product within a particular time period. For e.g.,what was the average sales of Oranges during the period March to June?
    Similarly, on changing the words "Oranges","March", and "June" in 3 adjacent cells, to, say, "Lemons","April","August", I get the result of "Average sales of Lemons for the Period April to August".

  28. Sheet 1
    Collected by Designation Ref CR/DR Credit

    S&L Head Of Unit 8089 C 250,000
    Hannington Manager 8090 C 400,000
    refund Team Leader 8091 C 471,805
    PSU Officer 8092 C 488,583
    Alex Head Of Unit 8093 C 500,000
    Hannington Manager 8094 C -
    refund Team Leader 8095 D 157,261
    CL Officer 8096 D -
    Joseph Head Of Unit 8097 C 300,000

    Summary sheet

    Name Grade Total

    Hannington Head Of Unit ......
    Alex Manager ......
    Joseph Team Leader ......
    Jimmy Officer ......

    I would like to get cummulative collection per collector in the summary sheet.

  29. Thanks for this insight. I request to be assisted I have two excell sheets both having a name and designation, I would like to summarise the amounts collected per name because we run a credit collections firm and we would like to award incetives.

    Joshua

  30. plz help me how to In this sheet A1 to F1 value are below 18 its is BOLD and G1 = total of A1:F1 and H1=percentage of G1
    i1= below 18 value of total numbers
    I want if i1=0 then show the value of H1 or average of G1 and If i1 is not euqal to zero(0) show value of i1 or below number of values

  31. I am trying to use a combination of SUMIFS and VLOOKUP but I can't seems to get it to work. Usually, this or other forums can always give you an example of someone who has had the exact same issue, but in this case I can't find a solution among previous examples. So, need some expert help on this :)

    I have two seperate data files, these are extractions from our order program, and they are two different 'reports' from this order system.
    The problem is that each file contains different type of information about the orders, and I need to merge them by the help of formulas.

    A simplified example (the original file contians alot of data, and this calculation needs to be done once a week, so therefore I would rellay like to able to 'automize' it):

    In the first file, columns of interest are: Order number, Order value, Seller-ID.

    In the second file, columns of interest are: Order number, Order method

    I want to in a seperate sheet (or workbook) be able to summarize the total order value for each Seller-ID, depending on if the order has been created by phone or by mail.

    I have been trying with SUMIF and including a VLOOKUP inside, since I need to make a cross-reference between the first and the second file, using order number as the common factor.

    Any thoughts or ideas?

  32. My query is after using this functions is there a way to view which all values are getting added to a cell after using the "SUMIF" function. Eg: my cell displays 800 which is derived using SUMIF function & 800 displayed by adding 3 enries 400,300,100
    how can i view these values?is there a way to it.
    kindly guide for the above query.

  33. Hello! I have two spreadsheets now
    Sheet1 contains countries and other datas

    sheet2 contains a full list countries and their respective regions
    e.g.
    Afghanistan Asia
    Albania Europe and Central Asia
    Angola Africa
    Antigua and Barbuda Americas
    Argentina Americas

    *my question is how can I define countries by region in SHEET1 by using the list in sheet2

  34. sheet 1

    Invoice number-------Invoice Date------- Invoice Amount------ Total Payments
    123121---------------02/10/2014----------50,000---------------?????
    524514---------------05/11/2014----------80,000---------------?????
    659874---------------08/11/2014----------60,000---------------?????

    sheet 2

    Invoice Number-------Payment Amount
    123121---------------5,000
    659874---------------6,000
    659874---------------7,000
    123121---------------3,000
    524514---------------2,000
    524514---------------6,000

  35. dear

    can you help me applying this formula plz

    i have two sheets
    1. invoice sheet
    2. payment sheet

    Invoice sheet has below

    Invoice number Invoice Date Invoice Amount Total Payments
    123121 02/10/2014 50,000 ?????
    524514 05/11/2014 80,000 ?????
    659874 08/11/2014 60,000 ?????

    Now the Since the payments are in installments which are present in another sheet

    Invoice Number Payment Amount
    123121 5,000
    659874 6,000
    659874 7,000
    123121 3,000
    524514 2,000
    524514 6,000

    Now i Want to apply vlookup in sheet 1 taking totals of payments against each invoice

    Kindly help me with this thank you

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

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

  38. Please give the four arguments when using the VLookUp Function.

  39. Svetlana,

    That is exactly what I was looking for! Thank you for your help.

    Thank you,

    Anya

  40. Hi,
    I read your post and didn't understand it fully. Although that's because i am searching for something else to work with. My problem is this particular excel sheet.

    Delivery date Delivery amount Current Date Maturity
    29-10-14 2,675.00 21-11-14 23
    29-10-14 4,320.00 21-11-14 23
    29-10-14 1,235.00 21-11-14 23
    29-10-14 1,235.00 21-11-14 23
    07-11-14 1,636.00 21-11-14 14
    06-11-14 26,499.60 21-11-14 15
    07-11-14 1,440.00 21-11-14 14
    11-11-14 48,293.00 21-11-14 10
    06-11-14 24,888.00 21-11-14 15
    11-11-14 60,092.08 21-11-14 10
    11-11-14 46,552.80 21-11-14 10
    11-11-14 16,054.80 21-11-14 10
    11-11-14 25,937.58 21-11-14 10
    11-11-14 24,888.00 21-11-14 10
    11-11-14 26,484.46 21-11-14 10
    11-11-14 11,638.20 21-11-14 10
    11-11-14 11,382.28 21-11-14 10
    12-11-14 22,680.48 21-11-14 9
    12-11-14 40,007.80 21-11-14 9
    12-11-14 11,963.28 21-11-14 9
    12-11-14 11,460.00 21-11-14 9
    12-11-14 4,416.60 21-11-14 9
    13-11-14 13,301.88 21-11-14 8
    16-11-14 24,888.00 21-11-14 5
    19-11-14 22,083.00 21-11-14 2
    19-11-14 6,329.88 21-11-14 2
    15-11-14 22,341.40 21-11-14 6
    Here, i need the current date to calculate maturity when maturity term is 21 days and then i need to sum up total delivery without those amounts that have been paid and have not matured yet. I tried using VBA editor to exclude colored cells but so far i can only add colored cells but not meeting the criteria of 21 days of maturity. Please help if you can.
    Advance thanks
    Shahriar

    1. Hello Abul,

      I believe your date did not post correctly here. So, if you send a sample workbook to support@ablebits.com and include the result you want to get, our support team will try to help.

  41. Hi, I have this table I want to add the total numbers in column titled AL if the equivalent date is current year only and dont sum up the old years. Example

    Total Leave Used: ____ (here I need the formula cell I9)

    FROM UNTIL EMP REASON AL MC HL UL LATE
    04/07/2012 04/07/2012 21 ANAK SAKIT 0 0 0 1 0
    15/07/2012 15/07/2012 21 PERSONAL MATTER 0 0 0 0.5 0
    25/07/2013 26/07/2013 21 RAYA HOLIDAY 0.7 0 0 0 0
    30/07/2014 31/07/2014 21 RAYA HOLIDAY 2 0 0 0 0
    01/08/2014 02/08/2014 21 RAYA HOLIDAY 1.5 0 0 0 0

    If the year in 1st column and second column is equal to current year (this year 2014) then calculate all the AL column ELSE if got 2013 or 2012 dont count the AL. Count only current year so if 2015 the 2015 only will count and answer will be on cell I9.

    Thank you.

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

  43. BRAND BATCH Q P N PROD.
    MPLW(CLS) 30 0 0 171
    MPLW(CLS) 31 0 0 1102
    MSW 89 947 1280 1504 3731
    SPW 12 0 62 337 399

    ABW 11 0 0 304 304
    MSW 89 0 550 1075 1625
    MSW 90 0 930 1818 2748
    SPW 12 0 0 404 404

    MSW 90 1071 0 2223 3294
    MPLW 32 0 1563 677 2240
    RCDW 10 365 122 0 487

    MSW 90 500 600 1188 2288
    MSW 91 591 857 1866 3314
    RCDW 10 0 0 346 346

    MSW 91 0 0 2466 2466
    MPLW(CLS) 32 693 0 0
    SPW 12 300 350 0 650

    MSW 91 0 0 1500 1500
    ABW 11 49 100 155 304
    SRW 18 0 0 120 120
    SPW 12 0 0 238 238

    MSW 91 0 0 1097 1097
    MSW 92 1070 0 2183 3253
    ABW 11 325 0 0 325
    ABW 12 0 225 0 225

    MSW 92 0 1310 2911 4221
    ABW 12 0 133 224 357
    RCW D 10 0 214 0 214

    how i calculate batch wise total vale from above sheet

    BATCH Q P N
    RCDW 10 value
    SPW 12
    SRW 18
    MPLW(CLS)
    31
    32
    MSW 89
    90
    91
    92

  44. Hello,

    I have a question regarding summing together values on a given date from multiple sheets to a summary sheet. I have tried a sumproduct but keep getting a #REF. The date is in the same column in each sheet (Column M) and the amount to be summed will be in the corresponding column P so I need to Vlook up that date. The date is in different cells each time. I have numerous dates and 50 sheets to sum so do not want to have to sum them manually.

    Any help that you could provide would be much appreciated.

    Thanks
    Andrew

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

  46. Hello,
    I have question regarding the last method,VLOOKUP and SUMIF.
    How would you write the formula if there is only one table (main table) and you want to find the sum of all the corresponding values of a particular ID?

    Help me out?

    Thank you.

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

  48. Svetlana,

    Could you please further explain your Tip above referencing [@Product] as a table? I don't understand exactly what steps are being taken, what you are attempting to accomplish or how it saves time. I feel like I might need to know this timesaver.

    1. Hello!

      I simply converted my range of cells (B1:C9 in that example) into a table. To do this, just select the cells, go to the Insert tab and click Table.

      "Product" is the header of column A, as you can see in the screenshot. When typing a formula in the formula bar, Microsoft Excel inserts the table column header automatically (with the preceding @) once you type a cell reference that belongs to your table.

      As for saving time, when you write or copy a formula in just one cell of the table, the entire column fills down with the same formula automatically.

  49. Hi Svetlana,
    Your site is excellent and a great help...my question is 16 columns of data as follows:
    Col 1 - Lookup unique item
    Col 2 - Data Value
    Col 3 - +/- adjustment to col 2 value
    Col 4 - Adjusted value
    Cols 5, 6, 7 -- Same as Col 2-4
    Cols 8, 9, 10 -- same as above and pattern repeated 2x more (so a total of 5 times)
    What I need to do is find the min and max adjusted values, and exclude them then calculate the average of the remaining 3 adjusted values.
    I have created array formulas to identify min and max values
    {=MIN(VLOOKUP($A4,$A$4:$Q$21,{4,7,10,13,16},FALSE))} same thing for max values. But dont know how to construct an AverageIF statement to first exclude max and min values then calculate the average of the remaining 3 values in each row for each unique lookup item. Can you help or do you have any suggestions.
    Thanks much

  50. Hi Svetlana Cheusheva,

    I am stucked in problem , where I have cheq number and Amount in one sheet 1
    and in another sheet 2 also I have cheq number and Amount.I want to find the records that have cheq number and Amount ( both) matching in sheet 1 , when we compare two sheets. I tried using diff formulas but did not get the desired result. Can you please help.

    Thanks in Advance.

    Atul

      1. Hi,
        I am working with a huge spread that consists of only figures. There are various expense lines coded with debit and credit amounts. Can you kindly suggest to me which formula to use in summing the debits and credits of each expense line in another spread sheet using their respective expense codes.

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