Comments on: How to use VLOOKUP with SUM or SUMIF functions in Excel

In this tutorial, you will find a handful of advanced formula examples that demonstrate how to use Excel's VLOOKUP and SUM or SUMIF functions to look up and sum values based on one or several criteria. Continue reading →

Comments page 3. Total comments: 200

  1. Svetlana,

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

    1. Hello!

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

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

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

  2. Hi Svetlana Cheusheva,

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

    Thanks in Advance.

    Atul

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

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

    1. 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. Hi Amit,

    Sorry, I do not understand what exactly formula you want. Anyway, you can download a corresponding Excel workbook (a download link is provided at the end of each example) and copy the formula from there.

    1. Hi,
      Pls. send how to calculate sume based on vertical & Horizontal criteria

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

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

      1. thanks for correction on sorting error, but did'n explain why need to sorting required? please explain.

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

        1. 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. Wow! I would never think of using TRANSPOSE. Thank you very much for sharing!

Post a comment



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