SUMPRODUCT and SUMIFS with multiple criteria in multiple rows

avensis1

New Member
Joined
Feb 24, 2017
Messages
3
Hi,

I have the data as in the table 1 below. I then want to create a table where one can write in make, type, year and then get the summarized price for this combo of cars, see table 2. I'm having problems to get the formula working.. What I have done is the following in cell B2 in Table 2

=SUMPRODUCT(SUMIFS(Table1!E:E;Table1!B:B;Table2!C2:D2;Table1!C:C;Table2!E2:F2;Table1!D:D;Table2!G2:H2))

However, this does not work. It seems to be that I need to have the criteria data in rows and columns respectively, but I see two problems here...

1.
I have it in rows, and for outstanding reasons, I will have to have them in rows

2.
I do have three sets of multiple criteria - but it seems like SUMPRODUCT is 2-dimensional, i.e. I can not have a third dimension of data like I have.

So, smart and helpful people out there - what can I do?

Thanks!



Table 1
--------------------------------------------


ABCDE
1MAKETYPEYEARPRICE
2FordSmall201310000
3FordMedium201415000
4FordLarge201520000
5ToyotaSmall20135000
6ToyotaMedium201410000
7ToyotaLarge201515000
8NissanSmall20137000
9NissanMedium201412000
10NissanLarge201517000

<tbody>
</tbody>

















Table 2
--------------------------------------------

ABCDEFGH
1TOTAL PRICEMAKE 1MAKE 2TYPE 1TYPE 2YEAR 1YEAR 2
2WHAT FORMULA HERE????FordNissanMediumLarge20132015
3
4

<tbody>
</tbody>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,621
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to the forum!

You can try like this:

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">TOTAL PRICE</td><td style=";">MAKE 1</td><td style=";">MAKE 2</td><td style=";">TYPE 1</td><td style=";">TYPE 2</td><td style=";">YEAR 1</td><td style=";">YEAR 2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;background-color: #FFFF00;;">37000</td><td style=";">Ford</td><td style=";">Nissan</td><td style=";">Medium</td><td style=";">Large</td><td style="text-align: right;;">2013</td><td style="text-align: right;;">2015</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">Table1!$D$2:$D$100,(<font color="Red">Table1!$A$2:$A$100=C2</font>)+(<font color="Red">Table1!$A$2:$A$100=D2</font>),(<font color="Red">Table1!$B$2:$B$100=E2</font>)+(<font color="Red">Table1!$B$2:$B$100=F2</font>),(<font color="Red">Table1!$C$2:$C$100=G2</font>)+(<font color="Red">Table1!$C$2:$C$100=H2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />


<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #333333;background-color: #FAFAFA;;">MAKE</td><td style="font-weight: bold;color: #333333;background-color: #FAFAFA;;">TYPE</td><td style="font-weight: bold;color: #333333;background-color: #FAFAFA;;">YEAR</td><td style="font-weight: bold;color: #333333;background-color: #FAFAFA;;">PRICE</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;background-color: #FAFAFA;;">Ford</td><td style="color: #333333;background-color: #FAFAFA;;">Small</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2013</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;background-color: #FAFAFA;;">Ford</td><td style="color: #333333;background-color: #FAFAFA;;">Medium</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">15000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;background-color: #FAFAFA;;">Ford</td><td style="color: #333333;background-color: #FAFAFA;;">Large</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2015</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">20000</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;background-color: #FAFAFA;;">Toyota</td><td style="color: #333333;background-color: #FAFAFA;;">Small</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2013</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">5000</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;background-color: #FAFAFA;;">Toyota</td><td style="color: #333333;background-color: #FAFAFA;;">Medium</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">10000</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;background-color: #FAFAFA;;">Toyota</td><td style="color: #333333;background-color: #FAFAFA;;">Large</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2015</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">15000</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;background-color: #FAFAFA;;">Nissan</td><td style="color: #333333;background-color: #FAFAFA;;">Small</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2013</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">7000</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;background-color: #FAFAFA;;">Nissan</td><td style="color: #333333;background-color: #FAFAFA;;">Medium</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2014</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">12000</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;background-color: #FAFAFA;;">Nissan</td><td style="color: #333333;background-color: #FAFAFA;;">Large</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2015</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">17000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Table1</p><br /><br />
 

avensis1

New Member
Joined
Feb 24, 2017
Messages
3
Thanks, this seem to work just fine in this case. However, I have some problems when transferring to my real-world case, which in principle is built up the same way.

Does this solution have limitations on that each Make/Type etc needs to only have two values selected (in my case Ford & Nissan / Medium & Large / 2013 & 2014)? Or could I extend the selection to include e.g. also 2015?
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,621
Office Version
  1. 365
Platform
  1. Windows
Or could I extend the selection to include e.g. also 2015?

Hi, you can extend it, for example, where I2 contains the third year condition:

Rich (BB code):
=SUMPRODUCT(Table1!$D$2:$D$100,(Table1!$A$2:$A$100=C2)+(Table1!$A$2:$A$100=D2),(Table1!$B$2:$B$100=E2)+(Table1!$B$2:$B$100=F2),(Table1!$C$2:$C$100=G2)+(Table1!$C$2:$C$100=H2)+(Table1!$C$2:$C$100=I2))
 
Last edited:
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,679
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top