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:
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl66, width: 256, colspan: 4"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl66"]Item [/TD]
[TD="class: xl66"]WK1[/TD]
[TD="class: xl66"]WK1[/TD]
[TD="class: xl66"]WK2[/TD]
[TD="class: xl66"]WK2[/TD]
[/TR]
[TR]
[TD="class: xl67"]Car[/TD]
[TD="class: xl67, align: right"]20[/TD]
[TD="class: xl67, align: right"]15[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67, align: right"]15[/TD]
[/TR]
[TR]
[TD="class: xl67"]Boat[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]20[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl67"]Plane[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67, align: right"]15[/TD]
[TD="class: xl67, align: right"]15[/TD]
[TD="class: xl67, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl67"]Train[/TD]
[TD="class: xl67, align: right"]15[/TD]
[TD="class: xl67, align: right"]10[/TD]
[TD="class: xl67, align: right"]5[/TD]
[TD="class: xl67, align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Item[/TD]
[TD="class: xl65, width: 64"]Week[/TD]
[TD="class: xl65, width: 64"]Sales[/TD]
[/TR]
[TR]
[TD="class: xl66"]Car[/TD]
[TD="class: xl66"]WK1[/TD]
[TD="class: xl66"]???[/TD]
[/TR]
[TR]
[TD="class: xl66"]Car[/TD]
[TD="class: xl66"]WK2[/TD]
[TD="class: xl66"]???[/TD]
[/TR]
[TR]
[TD="class: xl66"]Boat [/TD]
[TD="class: xl66"]WK2[/TD]
[TD="class: xl66"]???[/TD]
[/TR]
[TR]
[TD="class: xl66"]Plane[/TD]
[TD="class: xl66"]WK2[/TD]
[TD="class: xl66"]???[/TD]
[/TR]
[TR]
[TD="class: xl66"]Plane[/TD]
[TD="class: xl66"]WK2[/TD]
[TD="class: xl66"]???[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Sheet 1:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Item[/TD]
[TD]WK1[/TD]
[TD]WK1[/TD]
[TD]WK2[/TD]
[TD]WK2[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Car[/TD]
[TD]

20​

[/TD]
[TD]

15​

[/TD]
[TD]

10​

[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Boat[/TD]
[TD]

5​

[/TD]
[TD]

20​

[/TD]
[TD]

5​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Plane[/TD]
[TD]

10​

[/TD]
[TD]

15​

[/TD]
[TD]

15​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Train[/TD]
[TD]

15​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[TD]

20​

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Sheet 2:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Item[/TD]
[TD]Week[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Car[/TD]
[TD]WK1[/TD]
[TD]

35​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Car[/TD]
[TD]WK2[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Boat[/TD]
[TD]WK1[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Boat[/TD]
[TD]WK2[/TD]
[TD]

15​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Plane[/TD]
[TD]WK1[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Plane[/TD]
[TD]WK2[/TD]
[TD]

20​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Train[/TD]
[TD]WK1[/TD]
[TD]

25​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Train[/TD]
[TD]WK2[/TD]
[TD]

25​

[/TD]
[/TR]
</tbody>[/TABLE]

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?