SUMPRODUCT and SUMIFS with multiple criteria in multiple rows

[avensis1]

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


[TABLE="width: 200, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MAKE[/TD]
[TD]TYPE[/TD]
[TD]YEAR[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Ford[/TD]
[TD]Small[/TD]
[TD]2013[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Ford[/TD]
[TD]Medium[/TD]
[TD]2014[/TD]
[TD]15000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Ford[/TD]
[TD]Large[/TD]
[TD]2015[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Toyota[/TD]
[TD]Small[/TD]
[TD]2013[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Toyota[/TD]
[TD]Medium[/TD]
[TD]2014[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Toyota[/TD]
[TD]Large[/TD]
[TD]2015[/TD]
[TD]15000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Nissan[/TD]
[TD]Small[/TD]
[TD]2013[/TD]
[TD]7000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Nissan[/TD]
[TD]Medium[/TD]
[TD]2014[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Nissan[/TD]
[TD]Large[/TD]
[TD]2015[/TD]
[TD]17000[/TD]
[/TR]
</tbody>[/TABLE]

















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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]TOTAL PRICE[/TD]
[TD]MAKE 1[/TD]
[TD]MAKE 2[/TD]
[TD]TYPE 1[/TD]
[TD]TYPE 2[/TD]
[TD]YEAR 1[/TD]
[TD]YEAR 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WHAT FORMULA HERE????[/TD]
[TD]Ford[/TD]
[TD]Nissan[/TD]
[TD]Medium[/TD]
[TD]Large[/TD]
[TD]2013[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the forum!

You can try like this:

Excel 2013
	B	C	D	E	F	G	H
1	TOTAL PRICE	MAKE 1	MAKE 2	TYPE 1	TYPE 2	YEAR 1	YEAR 2
2	37000	Ford	Nissan	Medium	Large	2013	2015
Sheet2

Cell Formulas
Range	Formula
B2	=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))

Excel 2013
	A	B	C	D
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
Table1

 

[avensis1]

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]

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:

=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))