Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns

Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns
Most examples of the SUMPRODUCT function show a single list of numbers being evaluated for a particular criteria. The following 6 pictures show a simple modification involving SUMPRODUCT, to demonstrate some versatility with that function.
The first picture shows a range of monthly sales of a few warehouse items that are listed as data validated criteria for cell A2. Here, I selected the criteria SKU 567GHI.
TTFE0015a
In the next picture, cell D2 sums the numbers to 6021 for January, February, and March that belong to SKU 567GHI with this formula that simply includes columns B, C, and D for the SUMPRODUCT range of inclusion:code>=SUMPRODUCT((A7:A100=A2)*B7:D100)
TTFE0015b
The next picture shows 567GHI summed to 6507 in cell F2 for April, May, and June with this formula:
=SUMPRODUCT((A7:A100=A2)*E7:G100)
TTFE0015c
The next picture shows 567GHI summed to 6433 in cell H2 for July, August, and September with this formula:
=SUMPRODUCT((A7:A100=A2)*H7:J100)
TTFE0015d
The next picture shows 567GHI summed to 8614 in cell J2 for October, November, and December with this formula:
=SUMPRODUCT((A7:A100=A2)*K7:M100)
TTFE0015e
Finally, SKU 567GHI’s total of 27575 in cell M2 for all 12 months is returned by this formula:
=SUMPRODUCT((A7:A100=A2)*B7:M100)
TTFE0015f

Share Button
Posted in Tom's Tutorials for Excel
Tags:
10 comments on “Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns
  1. Catarino says:

    The sumproduct function make our calculations easier. With this tutorial, you inspired us to love the power of excel. You made my life brand new. Thank you!

  2. MSG Scott Woods says:

    I have a question. I need to count data based on multiple criteria. I have a report that has about 9 columns. I need to count data based on whether or not data in 3 or more of those columns meet certain criteria. I know I can use =sum(countifs) for the columns where I need only I identify one of the data bits in that column. However, one if the columns contains data I need to match to a list of data from another tab. would be easier to show a sample but I dont see how to do that here..

  3. sandeep kothari says:

    Marvelous!

  4. K.SATYA HARI GOPAL says:

    Respected sir,
    Please send a download link to me “Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns” OR Video also. for better understanding.
    Thank you

  5. Noob says:

    what is the formula if i want to add by month as well

Leave a Reply

Your email address will not be published. Required fields are marked *

*