Sum index multiple criteria

[EmileEA]

Hi,

I have a big sales report which expands every week. It shows the sales in different weeks (CYWeek) for each of the 15 stores.

Now I want to make a graph to show the sales for each store during the year, so It's easy for me to see trends for all different stores.

I have the criteria CYweek in the first column and the store name in the rows (see example excel sheet attached).
A simple index match will give me the first match of the cyweek and the store name. But I need it to sum up for all weeks.

So in the attached example in url (Imgur: The most awesome images on the Internet) I want the following for Store 2:

201614: 11
201615: 17
201616: 12

Could anyone help me with the correct formula which also uses as little calculation power as possible since it's becoming a huge file.
My life would be so much easier if anyone could help me and I would be forever grateful

Regards,

Emile

 

[asia1558]

Hi Emile,

This is my solution, please test it.

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G	H	I	J	K
1			Store1	Store2	Store3	Store4			201614	201615	201617
2	201614		2	5	2	9		Store2	40	24	22
3	201614		3	8	2	7		Store3	17	21	18
4	201614		2	10	6	10
5	201614		2	8	3	7
6	201614		6	5	1	8
7	201614		9	4	3	1
8	201615		7	1	7	4
9	201615		7	6	2	10
10	201615		8	10	2	6
11	201615		10	6	4	6
12	201615		4	1	6	3
13	201617		2	5	2	1
14	201617		10	3	1	1
15	201617		6	7	5	9
16	201617		4	2	6	8
17	201617		4	2	3	10
18	201617		2	3	1	5
Sheet1

Cell Formulas
Range	Formula
I2	=SUMIF(OFFSET($A$1,1,0,COUNT($A:$A),1),I$1,OFFSET($C$1,1,MATCH($H2,$C$1:$F$1,0)-1,COUNT($A:$A),1))

Asia

 

[MARZIOTULLIO]

hi

i2=SUMPRODUCT(($A$2:$A$18=I$1)*($C$1:$F$1=$H2)*($C$2:$F$18)) copy across and down

 

[EmileEA]

Hi Emile,

This is my solution, please test it.

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G	H	I	J	K
1			Store1	Store2	Store3	Store4			201614	201615	201617
2	201614		2	5	2	9		Store2	40	24	22
3	201614		3	8	2	7		Store3	17	21	18
4	201614		2	10	6	10
5	201614		2	8	3	7
6	201614		6	5	1	8
7	201614		9	4	3	1
8	201615		7	1	7	4
9	201615		7	6	2	10
10	201615		8	10	2	6
11	201615		10	6	4	6
12	201615		4	1	6	3
13	201617		2	5	2	1
14	201617		10	3	1	1
15	201617		6	7	5	9
16	201617		4	2	6	8
17	201617		4	2	3	10
18	201617		2	3	1	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =SUMIF(OFFSET($A$1,1,0,COUNT($A:$A),1),I$1,OFFSET($C$1,1,MATCH($H2,$C$1:$F$1,0)-1,COUNT($A:$A),1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Asia

Thanks a lot for this solution, you made my day!

<3