Sum multiple columns based on multiple criteria

[chicubs]

Hello, first time user here! I am trying to calculate total sales for each line item in Sheet 2 from the information given in Sheet 1. For example, Line 1 (Car WK1), should have total sales of 35. I'm having trouble figuring out how to sum multiple columns with the same header by using sumifs and index/match for the item name and week given in Sheet 2. The formula needs to be able to be copied down in Sheet 2.

Sheet 1:

	Sales
Item	WK1	WK1	WK2	WK2
Car	20	15	10	15
Boat	5	20	5	10
Plane	10	15	15	5
Train	15	10	5	20

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Sheet 2:

Item	Week	Sales
Car	WK1	???
Car	WK2	???
Boat	WK2	???
Plane	WK2	???
Plane	WK2	???

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[mvervair]

I think just the =sumif(top range of sheet 1, cell containing test value on sheet 2(WK1 for example), range of the sales). I pasted what I did below.

A B C D E F G H I
Item WK1 WK1 WK2 WK2 Item Week Sales
Car 20 15 10 15 Car WK1 35
Boat 5 20 5 10 Car WK2 15
Plane 10 15 15 5 Boat WK2 20
Train 15 10 5 20 Plane WK2 =SUMIF($A$1:$E$1,H5, A5:E5)

Edit: formatting got all messed up. Hope the formula makes some sense.

 

[mvervair]

Oops I noticed unless the rows of items are lined up to match (ie Car is always on row 2 of the table on both sheets) cells can sum up on the wrong row. I guess you can put in a bunch of nested if statements, but that could be a huge pain if you have a bunch of skus, weeks of data, or rows. I made this nested if with sumif and it worked:

=IF($A$2=G2,SUMIF($A$1:$E$1,H2,$A$2:$E$2),IF($A$3=G2,SUMIF($A$1:$E$1,H2,$A$3:$E$3),IF($A$4=G2,SUMIF($A$1:$E$1,H2,$A$4:$E$4))))

I'm sure there's a different method someone else uses that is way cleaner and uses less memory than this way.

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​
1​	Sheet 1:
2​		Sales
3​	Item	WK1	WK1	WK2	WK2
4​	Car	20​	15​	10​	15​
5​	Boat	5​	20​	5​	10​
6​	Plane	10​	15​	15​	5​
7​	Train	15​	10​	5​	20​

<tbody>
</tbody>

	A​	B​	C​
1​	Sheet 2:
2​	Item	Week	Sales
3​	Car	WK1	35​
4​	Car	WK2	25​
5​	Boat	WK1	25​
6​	Boat	WK2	15​
7​	Plane	WK1	25​
8​	Plane	WK2	20​
9​	Train	WK1	25​
10​	Train	WK2	25​

<tbody>
</tbody>

Sheet2 c3=SUMPRODUCT((Sheet1!$A$4:$A$7=Sheet2!A3)*(Sheet1!$B$3:$E$3=Sheet2!B3)*(Sheet1!$B$4:$E$7))

copy down

 

[wetph00t]

Hi, to piggy back on the question. What if there are multiple criteria under item, and multiple criteria for time?