Aug
5

How to use VLOOKUP & 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.

Are you trying to create a summary file in Excel that will identify all instances of one particular value, and then sum other values that are associated with those instances? Or, do you need to find all values in an array that meet the condition you specify and then sum the related values from another worksheet? Or maybe you are faced with a more concrete challenge, like looking through a table of your company invoices, identifying all invoices of a particular vendor, and then summing all the invoice values?

The tasks may vary, but the essence is the same - you want to look up and sum values with one or several criteria in Excel. What kind of values? Any numeric values. What sort of criteria? Any : ) Starting from a number or reference to a cell containing the right value, and ending with logical operators and results returned by Excel formulas.

So, does Microsoft Excel have any functionality that can help with the above tasks? Of course, it does! You can work out a solution by combining Excel's VLOOKUP or LOOKUP with SUM or SUMIF functions. The formula examples that follow below will help you understand how these Excel functions work and how to apply them to real data.

Please note, these are advanced examples that imply you are familiar with the general principles and syntax of the VLOOKUP function. If not, the first part of our VLOOKUP tutorial for beginners is certainly worth your attention - Excel VLOOKUP syntax and general usages.

Excel VLOOKUP and SUM - find the sum of matching values

If you work with numerical data in Excel, quite often you have not just to extract associated values from another table but also sum numbers in several columns or rows. To do this, you can use a combination of the SUM and VLOOKUP functions as demonstrated below.

Suppose, you have a product list with sales figures for several months, a column per each month.

Source data - Monthly Sales:
Source data to look up and sum matching values

Now, you want to make a summary table with the total sales for each product.

The solution is to use an array in the 3rd parameter (col_index_num) of the Excel VLOOKUP function. Here is a sample VLOOKUP formula:

=SUM(VLOOKUP(lookup value, lookup range, {2,3,4}, FALSE))

As you see, we use an array {2,3,4} in the third argument to perform several lookups within the same VLOOKUP formula in order to get the sum of values in columns 2,3 and 4.

And now, let's adjust this combination of VLOOKUP and SUM functions for our data to find the total of sales in columns B - M in the above table:

=SUM(VLOOKUP(B2, 'Monthly sales'! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))

Important! Since you are building an array formula, be sure to hit Ctrl + Shift + Enter instead of a simple Enter keystroke when you finished typing. When you do this, Microsoft Excel encloses your formula in curly braces like this:

{=SUM(VLOOKUP(B2, 'Monthly sales'!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}

If you press the Enter key as usual, only the first value in the array will get processed, which will produce incorrect results.
The SUM and VLOOKUP formula returns the sum of values in columns B - M in 'Monthly sales' sheet.

Tip. You may be curious why the formula displays [@Product] as the lookup value in the screenshot above. This is because I converted my data to table (Insert tab > Table). I find it very convenient to work with fully-functional Excel tables rather than mere ranges. For example, when you type a formula into one cell, Excel automatically copies it across the entire column and in this way saves you a few precious seconds :)

As you see, using the VLOOKUP and SUM functions in Excel is easy. However, this is not the ideal solution, especially if you are working with big tables. The point is that using array formulas may adversely affect the workbook's performance since each value in the array makes a separate call of the VLOOKUP function. So, the more values you have in the array and the more array formulas you have in your workbook, the slower Excel works.

You can bypass this problem by using a combination of the INDEX and MATCH functions instead of SUM and VLOOKUP, and I will show you a few formula examples in the next article.

Download this VLOOKUP and SUM sample.

How to perform other calculations with Excel VLOOKUP function

A moment ago we discussed an example of how you can extract values from several columns in the lookup table and calculate the sum of those values. In the same fashion, you can perform other mathematical calculations with the results returned by the VLOOKUP function. Here are a few formula examples:

Operation Formula example Description
Calculate average {=AVERAGE(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table' and calculates the average of values in columns B,C and D in the same row.
Find maximum value {=MAX(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table' and finds the max value in columns B,C and D in the same row.
Find minimum value {=MIN(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table' and finds the min value in columns B,C and D in the same row.
Calculate % of sum {=0.3*SUM(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} The formula searches for the value of cell A2 in 'Lookup table', sums values in columns B,C and D in the same row, and then calculates 30% of the sum.
Note. Since all of the above formulas are array formulas, remember to press Ctrl+Shift+Enter to enter them correctly in a cell.

If we add the above formulas to the 'Summary Sales' table from the previous example, the result will look similar to this:

Use VLOOKUP with other Excel functions to sum all matching values in an array and find the average, min or max value.

Download this VLOOKUP calculations sample.

LOOKUP AND SUM - look up in array and sum matching values

In case your lookup parameter is an array rather than a single value, the VLOOKUP function is of no avail because it cannot look up in data arrays. In this case, you can use Excel's LOOKUP function that is analogues to VLOOKUP but works with arrays as well as with individual values.

Let's consider the following example, so that you can better understand what I'm talking about. Suppose, you have a table that lists customer names, purchased products and quantity (Main table). You also have a second table containing the product prices (Lookup table). Your task is to make a formula that finds the total of all orders made by a given customer.
Need a formula to find the total value of all products purchased by a given customer.

As you remember, you cannot utilize the Excel VLOOKUP function since you have multiple instances of the lookup value (array of data). Instead, you use a combination of SUM and LOOKUP functions like this:

=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))

Since this is an array formula, remember to press Ctrl + Shift + Enter to complete it.
A combination of SUM and LOOKUP functions that finds the total value of all products purchased by a given customer

And now, let's analyses the formula's ingredients so that you understand how each of the functions works and can to tweak it for your own data.

We'll put aside the SUM function for a while, because its purpose is obvious, and focus on the 3 components that are multiplied:

  1. LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)

    This LOOKUP function looks up the goods listed in column C in the main table, and returns the corresponding price from column B in the lookup table.

  2. $D$2:$D$10

    This component returns quantity of each product purchased by each customer, which is listed in column D in the main table. Multiplied by the price, which is returned by the LOOKUP function above, it gives you the cost of each purchased product.

  3. $B$2:$B$10=$G$1

    This formula compares the customers' names in column B with the name in cell G1. If a match is found, it returns "1", otherwise "0". You use it simply to "cut off" customers' names other than the name in cell G1, since all of us know that any number multiplied by zero is zero.

Because our formula is an array formula it iterates the process described above for each value in the lookup array. And finally, the SUM function sums the products of all multiplications. Nothing difficult at all, it is?

Note. For the LOOKUP formula to work correctly you need to sort the lookup column in your Lookup table in ascending order (from A to Z). If sorting is not acceptable on your data, check out an awesome SUM / TRANSPOSE formula suggested by Leo.

Download this LOOKUP and SUM sample.

VLOOKUP and SUMIF - look up & sum values that meet certain criteria

Excel's SUMIF function is similar to SUM we've just discussed in the way that it also sums values. The difference is that the SUMIF function sums only those values that meet the criteria you specify. For example, the simplest SUMIF formula =SUMIF(A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10.

This is very easy, right? And now let's consider a bit more complex scenario. Suppose you have a table that lists the sales persons' names and ID numbers (lookup table). You have another table that contains the same IDs and associated sales figures (main table). Your task is to find the total of sales made by a given person by their ID. At that, there are 2 complicating factors:

  • The mail table contains multiple entries for the same ID in a random order.
  • You cannot add the "Sales person names" column to the main table.

Look up and sum the values that meet your criteria

And now, let's make a formula that, firstly, finds all sales made by a given person, and secondly, sums the found values.

Before we start on the formula, let me remind you the syntax of the SUMIF function:

SUMIF(range, criteria, [sum_range])
  • range - this parameter is self-explanatory, simply a range of cells that you want to evaluate by the specified criteria.
  • criteria - the condition that tells the formula what values to sum. It can be supplied in the form of a number, cell reference, expression, or another Excel function.
  • sum_range - this parameter is optional, but very important to us. It defines the range where the corresponding cells' values shall be added. If omitted, Excel sums the values of cells that are specified in the range argument (1st parameter).

Keeping the above info in mind, let's define the 3 parameters for our SUMIF function. As you remember, we want to sum all the sales made by a given person whose name is entered in cell F2 in the main table (please see the image above).

  1. Range - since we are searching by sales person ID, the range parameter for our SUMIF function is column B in the main table. So, you can enter the range B:B, or if you convert you data to a table, you can use the column's name instead: Main_table[ID]
  2. Criteria - because we have sales persons' names in another table (lookup table), we have to use the VLOOKUP formula to find the ID corresponding to a given person. The person's name is written in cell F2 in the main table, so we look it up using this formula: VLOOKUP($F$2,Lookup_table,2,FALSE)

    Of course, you could enter the name in the lookup criteria of your VLOOKUP function, but using an absolute cell reference is a better approach because this creates a universal formula that works for any name input in a given cell.

  3. Sum range - this is the easiest part. Since our sales numbers are in column C named "Sales", we simply put Main_table[Sales].

    Now, all you need is to assemble the formula's parts and your SUMIF + VLOOKUP formula is ready:

    =SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
    SUMIF + VLOOKUP formula that looks up and sums values that meet the criteria you specify

Download this VLOOKUP and SUMIF sample.

Formula-free way to do vlookup in Excel

Finally, let me introduce you the tool that can look up, match and merge your tables without any functions or formulas. The Merge Tables Wizard add-in was designed and develop as a time-saving and easy-to-use alternative to Excel's VLOOKUP and LOOKUP functions, and it can be very helpful both to beginners and advanced users.

Instead of figuring out formulas, you simply specify your main and lookup tables, define a common column or columns, and tell the wizard what data you want to fetch.
Merge Tables Wizard - a formula-free way to do vlookup in Excel

Then you allow the wizard a few seconds to look up, match and deliver you the results. If you think this add-in may prove helpful in your work, you are most welcome to download a trial version : )

You may also be interested in:

203 Responses to "How to use VLOOKUP & SUM or SUMIF functions in Excel"

  1. Leo Wong says:

    Hello Svetlana Cheusheva,

    I am calculating something like you mentioned in the part "LOOKUP AND SUM - look up in array and sum matching values". And, I applied the formula you provided:

    {=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))}

    However, the outcome seems incorrect. I finally found that the LOOKUP function must be used with sorted lookup table (in ascending). In addition, the total amount that Lilly Smith (in your example) spended should be $122.59 instead of $106.45. The reason of discrepancy is caused by LOOKUP function returns wrong value when the lookup table had not been sorted.

    So, do you think is there still any ways that I can get the correct result using one formula only and without sorting the lookup table?

    Also, you mentioned that VLOOKUP function cannot be used in array formula but LOOKUP function can. I wonder is there some tricks that I can know immediately which function can be used in array formula or not??

    I really appreciate if you can reply me.
    Thank you.

    • Hello Leo,

      You are absolutely right about sorting the lookup table in ascending order. I completely missed this point when writing the formula, a shame to me and my apologies. I've added a corresponding note and updated the example. Thank you very much for pointing out this error!

      As for which functions can be used in array formula, I do not know such a trick. I can only say that MATCH does not work with arrays either, which is why the INDEX and MATCH combination that often helps when VLOOKUP fails does not work in this case. And I cannot figure out any way other than sorting the lookup table and using the LOOKUP and SUM functions.

      • Leo Wong says:

        Thank you for your reply and teaching!!!

        Also, my friend recently taught me a way to get this type of total amount. The following is the formula:
        {=SUM($D$2:$D$10*IF($C$2:$C$10=TRANSPOSE('Lookup table'!$A$2:$A$16),TRANSPOSE('Lookup table'!$B$2:$B$16),0)*($B$2:$B$10=$I$1))}

        The core is using TRANSPOSE function to make the vertical lookup table to be horizontal. As a result, we can multiple the vertical array (the Main table) and the horizontal array (the lookup table) to create a 2-dimensional array formula.

        • Wow! I would never think of using TRANSPOSE. Thank you very much for sharing!

        • Anonymous says:

          hi, I want to create an invoice on excel sheet. on another excel workbook I want to create my stocklist. how do I link the 2 together, sothat when I fill in the invoice, the stock on the other workbook automatically changes. please help.

  2. amit says:

    pls send the formula vlookup total amount in word

  3. Mixing two databases says:

    Hello Svetlana, I have the below database with more than 500K rows of information. I need to build a report with months in a row not columns, and from the DB get all sales per country by month and by code

    ColumnA ColumnB ColumnC ColumnD
    Month Country Code Qty_Shipped
    Apr-11 France 2525 15
    May-11 Switzerland 3121
    Jun-11 Poland 1852 987
    Jul-11 Italy 3315 159
    Aug-11 Russia 4597 654
    Sep-11 Holland 1548 289
    Oct-11 England 6594 264
    Nov-11 Norwegian 43626
    Dec-11 Spain 1825 123

    This is the model I need. Can you help me to build a formula?
    Country Code Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11

    The database I put only one month per year. The reality is that this is a database with 500K rows the smallest one.

    Can you help me with ideas?
    Thanks

    • Hello,

      If my understanding is correct, you need to get "Country Code Apr-11 May-11 Jun-11 Jul-11 Aug-11 Sep-11 Oct-11 Nov-11" from "Month Country Code Qty_Shipped". If so, you can create a pivot table based on your first table:

      1 Select your table.

      2 Insert a pivot table (go to INSERT > Tables > PivotTable).

      3 In the PivotTable Fields pane, drag the Code column to the ROWS section, the Month column to the COLUMNS section, and Qty_Shipped column to the VALUES section.

      If you get "Count of Qty_shipped" instead of "Sum of Qty_shipped", click on the field name, select "Value Field Setting" from the menu, choose Sum from the list "Summarize value field by" in the pop-up dialog and click OK.

      The result will be similar to this:

  4. Sarah says:

    Hi

    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.

    Thanks
    Sarah

  5. shoeb shaikh says:

    hi,
    this is shoeb and i want to make formula for below table for sum from date to date
    column a column b column c column d column e
    Location 16-Sep-14 17-Sep-14 16/09/2014 17/09/2014
    Axis Airoli 3rd 0 957 0
    Axis Airoli 4th 1113 1758 1080 result should be
    Axis Airoli 5th 1205 1237 686 =sum of above dates value
    Axis Worli 1st 1499 1790 1522 with match of column a
    Axis Worli 2nd 1433 1844 1402

    please help to build up this formula
    Regards
    shoeb

  6. michael says:

    Hi sir,

    There is a main table item list, i need to SUM up QTY by each item of month from raw table. I have difficulty to bring out a raw table data which show all items of a year with Item Number, QTY and Date. Pls advise.

  7. Nivi says:

    Hi Svetlana,

    Thank you for the awesome tutorial. It is very helpful.

    I am working on something similar in your Lookup & Sum example for my research project.
    I need to find the sum of quantity of a particular category per customer. In lookup table I have customer id in one column, quantity in another and category in third column. And in the main table, customer Id and all the categories horizontal way(rows). So my task should be find a the total quantity for a category for a customer. Can you please help me with this?
    Thank you.

  8. Albert says:

    Hi,

    was wondering if you could help me with a an issue I have using SUMIF function. Range B10:B264 I have a list of item names, it can be up to 5 diffent item names. I have a single cell with one of those item names in it on Cell B278. I have some columns for calculations, stage #, straps, deliveries and usage amount (H10:H264) per item name. every row from 10 to 264 has all that information for for every stage # for that group of items, not following any particular order. can be up to 50 stages per item. what I need is a sum of the usage amount for one particular item name throught all the stages, item names and stage #'s come in no particular order. this is what I came up with:

    =SUMIF(B10:B264,"=B278",H10:H264)

    but it gives me a " 0 " amount as the result. now the usage amount range is based on a formula itself, but provides the number I need to sum throughout.

    I also tried to use this other function:

    =SUMIF(B10:B264,VLOOKUP(B278,B10:H264,7,FALSE),H10:H264)

    but got the same result as the previous mentioned formula.

    You think you can help me out?

    Thanks

  9. hari hasanudin says:

    Thanks for sharing tutorial, helpulness..

  10. Henri says:

    I am trying to do the following which I think is fairly basic:

    I have two reports. One is a budget file the other tracks sales.

    In the report that tracks sales I want to sum the YTD budget from the budget file on any given month ,when the cell in question returns the month.

    Any advice gratefully received.
    H

  11. Prashanth says:

    i Have Two Sheets from which i need to use Vlookup Formula to get the Value, But i dont know to add Multiple items,

    Sheet 1

    Staff# Sales Commission
    1001 100000 1000
    1002 20000 200
    1003 30000 300
    1004 10000 100
    1001 20000 200
    1005 10000 100
    1006 30000 300
    1007 20000 200
    1001 20000 200

    Sheet 2

    Staff # Formula Commission
    1001 =VLOOKUP(A2,Sheet1!$G$3:$I$11,3,0) 1000
    1002 =VLOOKUP(A3,Sheet1!$G$3:$I$11,3,0) 200
    1003 =VLOOKUP(A4,Sheet1!$G$3:$I$11,3,0) 300
    1004 =VLOOKUP(A5,Sheet1!$G$3:$I$11,3,0) 100

    how add multiple amounts in this issue.

  12. Atul says:

    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

  13. Ande says:

    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

  14. MsDClark says:

    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.

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

  15. Sahal says:

    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.

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

  16. Sayyid says:

    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.

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

  18. Andrew says:

    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

  19. arvind kumar sinha says:

    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

  20. Anya says:

    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

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

  21. Chona says:

    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.

  22. Abul Kashem Golam Shahriar says:

    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

  23. Anya says:

    Svetlana,

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

    Thank you,

    Anya

  24. Shey says:

    Please give the four arguments when using the VLookUp Function.

  25. Nitij Ad says:

    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?

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

  26. Peter says:

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

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

  27. hassan says:

    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

  28. hassan says:

    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

  29. S says:

    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

  30. jinesh sakaria says:

    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.

  31. Emma says:

    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. Ashok says:

    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

  33. Muhama says:

    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

  34. Muhama says:

    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.

  35. Abhi says:

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

  36. KHAN AFZAL says:

    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

  37. noman says:

    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

  38. Cheryl says:

    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 ☺

  39. Rachelle says:

    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.

  40. Grant says:

    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?

  41. Sam says:

    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.

  42. Suresh says:

    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)

  43. Leah says:

    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

  44. afrasiab says:

    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

  45. Sergei says:

    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

  46. Eric Peterson says:

    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

  47. Eric Peterson says:

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

  48. Norm Staswick says:

    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

  49. amy says:

    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

  50. Scott says:

    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.

  51. Vickey says:

    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

  52. Bob T says:

    Thank you!

  53. Wes says:

    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.

  54. Steven says:

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

  55. Vivien says:

    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

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

  57. Vladamir says:

    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

  58. Vladamir says:

    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

  59. MUHAMMAD RIYAS says:

    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.

  60. mei says:

    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

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

      • Anonymous says:

        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

  61. Vladamir says:

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

  62. Silas Alexander says:

    Hi Svetlana!
    I have a column of numbers from which I want the top 10% of the sum of the column to be highlighted. I tried the conditional formatting option. But it doesn't give me a result which is even near 10%.

  63. Jiten says:

    hi,
    i have a table in my excel file having different Items date wise, eg:-
    Date No. Name Item quantity
    1/4/15 106 Ab Apple 50
    1/4/15 129 Bd Mango 75
    1/4/15 235 Jp Mango 39
    2/4/15 114 Tk Mango 63
    2/4/15 228 Kl Orange 24
    3/4/15 0 0 0 0
    4/4/15 0 0 0 0
    5/4/15 235 Jp Mango 52
    5/4/15 106 Ab Orange 28
    ...
    .
    .
    .
    .
    and so on date wise, actually i want to monthly date wise quantity in one cell.
    which formula i have to use if i need the quantity of Mango for selected date?
    eg. if i put a date 1/4/15 and match with item Mango, so that in a one cell the total quantity should be 114.

  64. Elijah says:

    Greetings, I am trying to build a workbook that has 3 sheets. One for entry of first last name and ID number as well as a monetary value like a tip. On the second sheet is the summary page where i wanted the table to return the sum of all information by employee id to give me a total of tips for that employee. The third page i had set up as the reference table where i would enter the First last name and ID number. I tried using your example but still cant get it to work.

    I have pasted the formula below.

    Can you help?

    Im also using google documents

    ={=SUM(Tips!$c$2:$c$30*IF(Tips!$C$2:$C$30=TRANSPOSE(Employee!$c$2:$c$30),TRANSPOSE(Employee!$c$2:$c16),0)*($C$2:$C$2=$I$1))}

  65. Dan says:

    I have a combination of value such as '1/3 in A1, '3/3 in A2 and '3/6 in A3. Is there a way to sum the that are 1+3+3 so that in the cell I would have 7 and the next cell 3+3+6 that would equal to 12? Basically, I need to sum the 1st values of the A1, A2 and A3 and in the other cell 2nd values of the same cells?

  66. madhav says:

    Hi,
    I have a budget sheet where there is certain budget is allocated in different months under a certain budget heading. And I have a reporting month cell where I have a dropdown box including the names of the month. Now, what I want is to get the result of the sum of the budget of the activity when i respectively change the month. I also want to get the value, if I click the month to march, then i need the cumulative budget value added till march.

    Please help

  67. Tawnya says:

    Hello,

    I am working on an extensive spreadsheet with thousands of loan numbers, names, document types, ID numbers, etc. My issue is that each week, this company will send me a spreadsheet of thousands of loans but could very well be duplicates of the previous week. I cannot simply use the function "remove duplicates" because the loans are always duplicated with each separate document request and if I have one loan repeated 3x because it requires 3 different document types, that function will remove 2 of them and throws everything off. How can I weed out the prior week's loan numbers and simply identify what loans are new to the current weeks' spreadsheet? Sorry to be so longwinded. :-)

  68. Ishita says:

    hi,

    I want to add the individual values of each week for all the months in a year for a list of items.
    I need these to be calculated automatically in a different sheet. Could you help me out with this.
    For example, the main sheet looks something like the below table:
    5Jan - 9Jan 12Jan - 16 Jan etc
    Opening count 1 2
    New 5 7
    Closed 4 6
    Backlog 2 3

    In sheet2:

    I need the following result:
    Jan
    Opening Count 1
    New 12
    closed 10
    Backlog 3

    This needs to be done for all 12 months.

  69. Alagu says:

    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.

    • Thu Hà says:

      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

  70. krishna says:

    PURCHASE
    SL.No GRADE FINISH THIKNESS WIDTH HEIGHT GRAVITY NO COIL QTY
    1 304 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    2 316 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    3 321 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    4 309 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    5 LN-1 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    6 LN-4 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    7 200-S 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    8 SA 15.84 0.6 1500 2200 0.000008 1 Yes 15.84
    9 430 15.84 0.6 1500 2200 0.000008 1 Yes 15.84

    I need the vlookup formula for the above.

    you can see in the second coloumn "GRADE" that is the order may apper many times and need to add the vales of grade. Value is in 'QTY' column. pls let me know how I can do the look up formula.

  71. Navneet says:

    Hi Svetlana,

    Need your expertise again!!! I am suppose to make performance meter but can't get the desired results. Please have a look;

    Year Month Name leads Matured Conversion%
    2015 June Anju 30 2 7%
    2015 June Rekha 28 2 7%
    2015 June Somya 35 2 6%
    2015 July Anju 40 4 10%
    2015 July Rekha 35 5 14%
    2015 July Somya 45 5 11%

    How to get the average of Conversion% based upon extracting data from below selection:
    Select [From Month/Year---> to Month/Year] eg; [2015/June--->2015/July]
    Select Name eg; Anju
    Result should show Average% of selected Name based upon year and month range selected.

    I have been trying vlookup, index & match, sumif but didn't get through.

  72. Thu Hà says:

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

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

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

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

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

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

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

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

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

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

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

  73. Anshuman says:

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

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

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

    I want the result to be like this

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

    can someone help me on this. thanks in advance

  74. Farooq says:

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

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

  75. Brian says:

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

  76. Watt Daniel Moses says:

    Very good site , very educative

  77. Dev says:

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

    Thanks

  78. Erez says:

    Hi

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

  79. shubham says:

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

    Hi Svetlana,

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

  80. Ali Adnan says:

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

  81. Debbie says:

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

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

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

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

    I want to now total the categories for each semester

    Categories
    GE
    Surveys
    Lower Division

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

    GE 15 courses
    Surveys 10 courses
    Lower Division 9 courses

    Any help you can give me will greatly appreciated

  82. Dennis says:

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

  83. Ravi says:

    Hai,

    Please send how to calculate excel to excel sumif+vlookup

    total qty required in one format

  84. Ravi says:

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

    same type but different excell

  85. Gary says:

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

  86. st3ve says:

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

  87. Nicolas Blanco says:

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

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

  88. JJP says:

    If I have a table with the following:

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

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

  89. yousaf says:

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

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

    so i need a formula to get total marks sum

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

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

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

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

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

    waiting for a favourable response

  90. Gk says:

    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

  91. Dung Nguyen says:

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

    A B C
    15 18 50

    B C A
    13 19 23

    C A B
    30 15 20

  92. Dung Nguyen says:

    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.

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

  93. Helen says:

    Hi

    I have managed to get a sumif and combine it with a vlookup and that seems to give me the answers I need, but what I want to do is know when the values I've added reach a defined range of 335-395, and then return me the cell which falls in that range

    Any suggestions would be great!!

    Thanks

  94. Jim says:

    Good afternoon!
    I have a task in which I will be referring to two sheets. On the first sheet, I will have a name column (A1:A200) and a quantity column (B1:B200).
    On the second sheet, I will also have a name column and a quantity column, A and B respectively. My task is to search each name of sheet1!A1:A200 for a matching name on sheet2! Where there is a name match, I need to have the quantities added together. I hope you can assist me!
    thank you!

  95. Ramki says:

    Hi Jim,

    I have emailed you the excel sheet with the solution which would be more clear to you. Moreover I am not able to attach the excel file in this blog.

    Regards,

    Ramki

  96. NISAR AHMAD says:

    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

  97. rahul says:

    Hi Maria,
    i have 3 sheets in each three sheets column A is the common and different no. how do i get these no. not get repeated in my another "final" sheet.
    Second, i want to get the sum of each 3 sheets column A representing data to Final sheet.
    So, for First result i tried =IFERROR(VLOOKUP('1st Month'!$D4,'2nd Month'!D4:D22,1,0),IFERROR(VLOOKUP('2nd Month'!$D4,'3rd Month'!D4:D23,1,0),IFERROR(VLOOKUP('1st Month'!$D4,'3rd Month'!D4:D25,1,0),"0"))) formula and not sure if this right.
    for second Result i Tried = SUMPRODUCT(SUMIF(INDIRECT(""&test&"''!$D$4:$D$19"),$D4,INDIRECT(""&"'!$I$4:$I$19")), Pls Help me in getting the expected answer. Thanks

  98. rahul says:

    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

  99. Steven says:

    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.

  100. usman says:

    i have some invoices in sheet1 and i want to make a summary with product name and Quantity in sheet2

    so please help me to solve this problem detils are

    PRODUCT QTY
    AREIL 45G 96
    AREIL 500G 12
    SAFEGUARD 115G W 72
    SAFEGUARD 115G L 144
    H&S 200ML CC 6
    H&S 200ML B 12
    PANTENE 90ML SS 24

    PRODUCT QTY
    JELLETT BLUE 2+ 24
    MACH 3 RAZOR 3
    AREIL 45G 24
    AREIL 1000G 6
    SAFEGUARD 115G W 144
    AREIL 500G 6

  101. Michael Hogenbirk says:

    Hello hope that someone can help me? i need a formula that allows me to sum up the hours worked a day, the problem is it needs to be by name for example.

    harry worked from 09:00 till 09:30
    carlos from 10:00 till 10:30.
    etc

    how can i make excel add the worked hours behind carlos his name?

    thank you

    this is for a working schedule that sums up the worked hours as well.

  102. zouheir eid says:

    helo,
    please I need your help.

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

    Appreciate your support

  103. Rehan Rafiq says:

    I need your help, I just want to one cell contain text in other cell list i think formula =ISNUMBER(FIND(E47,B3:B991)) in this E47 is my targeted cell

  104. jalal says:

    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

  105. Rahul Mandale says:

    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

  106. farhad says:

    HI
    I want to count the value in subgroup for example:
    A
    B
    C
    d
    d
    d
    C
    d
    B
    C
    d
    d
    d

    I want to know how many "d" are in the first "C" group
    Or how many "d" are in the first "B" group
    "Count "d" from current value to same value in column.
    so tnx

  107. Veena says:

    what formula i need to use for below

    sheet one - GL code, vendor name and Feb amount in column. One vendor has multiple GL codes
    Sheet two - Same has above but amount is for JAN
    I need to bring jan amount to sheet one next to feb amount to see the varience

    since one vendor has multiple gl codes just vlookup formula is not working. What i need to do here

  108. SANKER says:

    Hi
    Require a help

    I have a rows of values like this
    5000
    2000
    7000
    15161
    5000

    In another cell i have a value 25161

    In the rows if i add some way i should be getting 25161 ( example 5000+15161+5000) will match my 25161,

    Is there any formula to achieve this

  109. Zahed says:

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

  110. NIRAJ says:

    Hi,

    I have a excle worksheet. in that there are every month salary detail in different month sheet. I want to get total of salary of the year in one sheet against his/her name. How to do it with Vlookup formula. Kindly suggest me.

    Thanks...

  111. justin says:

    can i be able to do sum of the various values having base as sku code (ex:20001218 -1
    in the same i have the other one to )

  112. nimalka says:

    i need to calculate the average value for male and female students in class. but their title in different page and final mark in different page. need to do it using vlookup..

  113. satanjay says:

    hi,
    I am using 2 sheets with daily sales in sheet1 and inventory in sheet2. I want to put sum formula in sheet2 so that it pick an item no and goes to sheet1, locates its occurrence and than calculate from values in corresponding columns. But the tricky part are, first, values are in 3 columns - sale(-), return(+) and stock delivery(+). Second, sale for same item will happen on multiple days, so all will be factored in and entered before the item in inventory list.
    I have input an array formula with sum and vlookup in sheet 2. It gives value for only one column and one row.

    Please help.

  114. kerim says:

    Hi,

    I have 12 columns and thousands of rows. On columns I have (Years-Months-Factories-a-b-c-d-e-f-volume-sales-price)..What I want to do is, I want to calculate total volume for each factory(4 factory) by months in each year..

    Thx

  115. DIGVIJAY SINGH says:

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

  116. Nandan says:

    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

    • Noman Ahmad says:

      You can use Paviot Table. It will surely convenient for you.
      Paviot Table can be added from Insert tab.

  117. tushar says:

    one problem mostly i face when i update the advance of employee .the problem is . there are three column

    column(A) column(B) column(C)
    employee punch n. Advance
    mohit 54 1500

    mukesh 84 2500

    rahul 90 2000

    mohit 54 500

    sabir 92 5000

    is there any formula that add the amount that is double to a employee
    by a formula in excel sheet when i prepare salary and adjust the advance .

  118. GHAZI IRSHAD says:

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

  119. Noman Ahmad says:

    Hye,

    I'm meeting trouble in doing sum of different vlookup values. I try explain my problem with an example given below:

    | A |B |C |D |E |F
    1| 02-01 |48 |22 |38 |54 |98
    2| 03-01 |20 |105|111|50 |40
    3| 03-02 |35 |40 |67 |66 |901
    4| 03-03 |88 |50 |100|47 |200

    In given above table I want to sum all values vertically in column F with reference to Column A but want to add only that values of which code in Column A start with 03.

    Please guide me. You can mail reply on my mail ID if convenient. Thanks

  120. Freddy Sim says:

    I have been struggling to fulfill my boss requirement but still unsuccessful. Maybe someone can help me with the quickest way.

    I have a workfile with multiple sheets but each sheets uses the same format. Example:
    Sheet 1.
    Part Name: ABC
    Type of Defects 1-May 2-May 3-May 4 May
    Defects 1 10 100 50 60
    Defects 2 12 50 20 30

    Sheet 2 or 3 or 4 are the same as sheet 1 except
    Part Names are different

    I want to create a Summary page whereby I just need to key in the following criteria:

    Part Name : XXXX ( just key in the part number )
    Date Start : Select which start date
    Date End : Select which end date

    With this 2 criteria typed in, all the defects will automatically add up but still separated still into Defects 1 and Defects 2.

    In other words, the left hand column will still have Defects 1 and Defects 2 and so on.

    Can someone help me?

  121. Tlhogi! says:

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

    Regards

    Tlhogi

  122. dixit says:

    when i create invoice ...then automatic stock credit or debit.,amount plus in customer account.,when enter part number then automatic fill part discription....i wish this formula

  123. sumanta says:

    How to calculate catagory wise cummilative value like
    Mc. Qty
    Duke120. 3
    Duke 220. 4
    Deke 330. 3
    pulser 120. 5
    Pulser 220. 7
    Pulser 250. 8
    Now what im want is cummilative value of duke and cummilative value of pulser different wise but on a same column.

  124. Anky says:

    Hello Svetlana,

    What you have shown is simply amazing and I sincerely appreciate the time and effort you have put. Thanks a lot!

    I have a small query which I believe has no solution but would like your opinion on. So I am working on a case similar to SUMIF and VLOOKUP combination example above, but the difference is that my lookup array is spread across multiple worksheet and not just one main table. Any thoughts on how to resolve it? I was thinking using SUMIFS and VLOOKUP maybe, but not sure! :-(

    Anky

  125. carolaine saint-hilaire says:

    Hi,

    I have a fairly simple task that I just can't figure out a formula for it. I have a range of account # that I need to find and sum on one cell.

    For example,

    I need to sum account numbers 2001-2020. All accounts from 1000 to 3000 are listed in column B (in another tab within the same work book). Column C in that same tab has the account values that I need to sum.

    I tried the SUM-LOOKUP combo but sorting in ascending order is not an option for me. What other formula can I use to get to my desired result?

    I would really appreciate your help with this!

  126. saurabh says:

    Respected Able Bits Team;
    Please give me merge tables wizard excel 2003 .xls format

    Regards
    Saurabh Sharma

  127. Milnie says:

    Hi there,
    Your tutorial is good but i'm struggling to adapt it to my situation and wondered if you could help?
    I have a workbook for timesheets that contains 53 sheets, one for every week. Each sheet is formatted the same and shows the job number and job name in rows above one and other in the first column. These rows are then combined in the next column to show total hours worked, then the next columns represent the actual days of the week and hours worked per day.
    I would like to have a summary table at the end of the workbook that tells me total hours worked in the year and total hours spent per job number. i've tried a variety of sum and look up combinations and nothing has worked yet.
    any pointers you can make would be greatly appreciated.

  128. Ishitapathak says:

    Hi,
    I have to find sum of sale qnty using vlookup

    first sheet where purchase is
    Code Qnty
    NFSC 2
    NFCC 5
    NFDD 7

    Other sheet where sale of the month is (date wise sale)
    Code Qnty
    NFSC 1
    NFCC 1
    NFDD 1
    NFSC 1
    NFDD 1
    NFCC 1

  129. Philip Morris says:

    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?

  130. Sharif says:

    Hi.,,

    May I Know How To Add Number with the Help of Vlook_Up..

    For Ex:

    Sharat
    Fruits Numbers
    Apple 190
    Banana 111
    Mango 503

    Geeta
    Mango 113
    Banana 190
    Apple 190
    watermelon 190

    Ram
    Banana 128
    Apple 147
    watermelon 180
    Mango 114

    ..

    ...

    How i Can Add Like......

    Fruits Total Items
    Total Banana ?
    Total Apple ?
    Total Mango ?
    Total watermelon ?

  131. Khairunissa says:

    Hi
    I am trying to do a sumif. The criteria are on two separate tabs. I want to sum if the attribute is "adjustable", but this attribute is on a separate tab so I have to do a vlookup to see if it is adjustable or not. Once something is adjustable, then it should be summed up. I've tried the following two formulae, and they don't seem to work:

    =SUMIFS('LG dataset Subfund Method'!I$2:I$611,vlookup('LG dataset Subfund Method'!F2,'Attributes Matrix'!$A$2:$G$611,7,false),Adjustable)

    and

    =SUMIFS('LG dataset Subfund Method'!I$2:I$611,INDEX('Attributes Matrix'!$G$2:$G$611,MATCH('Attributes Matrix'!A2,'Attributes Matrix'!$A$2:$A$611,0)),Adjustable)

    Basically I'm trying to create a logic that says sum up the cashflows on the LG tab only if the attribute attached to the plancode is Adjustable and this part is on the Attributes Matrix tab.

  132. varun says:

    i have tried this with sumif(vlook up) but i dont get the esult and i cudnt find my error in formula

  133. Gina says:

    Hi,
    I am trying to combine a SUMIFS and a VLOOKUP. My data set is big, and I run into problems when a name that used to be in the data is deleted from the data. That is why I want the forumla to lookup at the ID number of the person in my spreadsheet first, and then match it with their value in the data tab. Some people are in there twice, same ID but different two different values. I was a forumala that will match to the first and then match to the second.

    Can someone help with this?

  134. Shazzad says:

    How can I remove N/A

    Sheet 1 Sheet2 Sheet 3 Sheet 4
    As=(Vloopup)+(Vlookup)+(Vlookup)+(Vloopup) from different sheet, one sheet have no value(Like Sheet 4), but other sheet have value(Sheet 1,2,3) its showing N/A. If I use IF(ISERROR(Vlookup), then showing 0. But its not true.How can i solve this problem

    Urgent

  135. Daniella says:

    Hi All

    I hope you are all well.

    I don't know if this is going to be a bit of a stupid question, and I hope I make myself as understandable as possible. :)

    Firstly, I am using Excel 2016 on Windows 10.

    This is the scenario:

    Book 1

    - I have one Excel workbook with individual invoices for multiple customers. For example, the most important info:

    Column A - Customer name (There are sometimes more than a few invoices for one customer)

    Column B - Date

    Column C - Invoice amount

    Book 2

    - I have another Excel workbook with the same as above, but they are credits relating to those invoices/customers:

    Column A - Customer name (Same as above - more than a few lines for one customer, many different customers)

    Column B - Date

    Column C - Credit amount

    Book 3

    - The other Excel workbook is a consolidation of the above. I need to consolidate the above by customer, by month (working on November 2015 to October 2016 MTD). I considered doing a VLOOKUP by customer, but I don't know how to do a SUM VLOOKUP to add together all invoices for one customer in a date range from Book 1 onto Book 3, then the same for the credits from Book 2 to Book 3, so that I can calculate the difference.

    I hope someone can assist me with this, and that I have made the question quite clear. :)

    Thanks in advance!

  136. Gary says:

    Hello,

    Is it possible to sum a row of numbers only if those numbers are from a formula? I am using a forecasting spreadsheet and any future figures are input manually by the budget managers as a 'guess'. However the actual spends for each month up until that point are found by using a VLOOKUP formula.

    Basically I want to only sum the numbers if they are from the VLOOKUP formula.

    Thank you for any help you can provide.

  137. Jody says:

    I have a spreadsheet for monthly/yearly overtime worked per job position at my company. People can also be charged time and a half or time and three quarters, depending on their schedule.

    I need a way to search column b for job position and column c for type of schedule and if those equal PositionA ScheduleA to add column f's numbers all together in Cell Y. If PositionA Schedule B, add them together in Cell Z.

    Is that too complicated for Excel?

  138. Md.Iqbal says:

    Hello Dear,

    i know sumif formula. But i don't no vlookup sum formula that means ID Value Total. for Example,
    Sheet1,
    ID Value
    A1 100
    B1 200
    C1 300
    A1 500
    B1 300
    C1 400

    and i needed ID wise sum A1=100+500=600.
    B1=200+300=500.

    Please help me.

  139. Pekka says:

    Hi, I hope someone can help. I would like to add up 2 sets of 2 columns with multiple codes for certain results.

    For example/

    Column A (company) consists of:#1 and #0
    Column B (company) consists of:for #1 - A,B,C,D,Z
    for #0 - F,G,H,I,J,K,L,M

    Column D (contact) consists of: #0, #1 and #2
    Column E (contact) consists of: for #0 - A, B
    for #1 - C, D, E, F, G, J
    for #2 - C,D,E,F,G,I,K,L,Z

    Thanks in advance

  140. Rohit Thukral says:

    how to use sum and v lookup farmula from multiple rows
    BANK 301370
    BANK 736310
    BANK 1473600
    CONST. 100
    CONST. 200
    CONST. 200
    CONST. 200
    CONST. 200
    CONST. 218
    CONST. 300
    CONST. 29000
    CONST. 50000
    CONST. 68800
    FREIGHT 50
    FREIGHT 200
    FREIGHT 450
    GENERAL 50
    GENERAL 200
    GENERAL 220
    GENERAL 250
    GENERAL 300
    GENERAL 380

    i want sprate total like summery

  141. Rajesh says:

    Hi all

    Could you please tell me how in range of say some amounts, how to arrive at a particular sum amount. Suppose I have 50 Invoice amount due from a customer. I received a payment which includes 10-20 invoices. how can i know which amounts add to that particular cheque amount. Could you please give me the formula. Thanks

  142. Edward says:

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

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

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

    Thanks again for your clear explanations!

    Ed

  143. Ritesh says:

    Hi

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

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

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

    Thanks in advance

  144. Frank says:

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

  145. Chris says:

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

    Thanks.

  146. tarun says:

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

  147. Adrian says:

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

  148. sadiki mtenzi says:

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

  149. Alex A says:

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

  150. MAXWEL says:

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

  151. Carl says:

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

    Category; Trx Date; VOLUME

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

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

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

    Thanks

  152. David Lawson says:

    Beautifully written and clear guide, thanks Svetlana!

  153. Patrice says:

    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?

  154. Patrice says:

    across rows in the same column

  155. Praveen Ravi says:

    Hi,

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

    Thanks
    Praveen

  156. Kim says:

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

  157. Darin says:

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

  158. Duman says:

    Hello Svetlana,

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

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

  159. yingkham says:

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

    Best regards,
    Ying Kham

  160. Viorica Franco says:

    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

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard