# SUMPRODUCT and SUMIFS with multiple criteria in multiple rows

#### avensis1

##### New Member
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
--------------------------------------------

 A B C D E 1 MAKE TYPE YEAR PRICE 2 Ford Small 2013 10000 3 Ford Medium 2014 15000 4 Ford Large 2015 20000 5 Toyota Small 2013 5000 6 Toyota Medium 2014 10000 7 Toyota Large 2015 15000 8 Nissan Small 2013 7000 9 Nissan Medium 2014 12000 10 Nissan Large 2015 17000

<tbody>
</tbody>

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

 A B C D E F G H 1 TOTAL PRICE MAKE 1 MAKE 2 TYPE 1 TYPE 2 YEAR 1 YEAR 2 2 WHAT FORMULA HERE???? Ford Nissan Medium Large 2013 2015 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
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 /></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
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
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:

Replies
8
Views
77
Replies
4
Views
95
Replies
6
Views
303
Replies
2
Views
81
Replies
3
Views
99

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

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?

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