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.
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)
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)
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)
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)
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)
Good depth
Thank you!
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!
Thank you!
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..
See if one of these pictured examples from my Excel Twitter page show what you are asking about:
https://twitter.com/TomUrtis/status/1052310919110377473
https://twitter.com/TomUrtis/status/1071902423973687296
Marvelous!
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
what is the formula if i want to add by month as well
It would basically be
=SUMPRODUCT((B4:B25=A2)*(D4:D25=M2)
where A2 holds the SKU and M2 Holds the month name.