sum function but row dependent on value

[EdE]

Hi. I have a table with various PN's in rows with different groupings (IE Forecast, Sales, Return ) and week numbers in columns on sheet 1. On sheet 2, I want to be able to summarize forecast by summing 3 weeks at a time by group by PN.
Data

PN	Week	1	2	3	4	5	6	7	8	9
123	Forecast	3	6	6	2	3	7	10	2	2
123	Sales	5	6	8	3	1	2	10	6	6
123	Returns	6	4	8	10	2	2	7	4	4
456	Forecast	8	6	8	9	3	4	7	9	9
456	Sales	3	9	3	10	4	8	10	4	4
456	Returns	1	1	2	1	6	10	1	2	2
789	Forecast	7	2	1	1	10	6	2	5	5
789	Sales	2	8	1	6	2	1	10	10	10
789	Returns	4	10	7	5	2	4	9	7	7

<tbody>
</tbody>

Result

Forecast	Weeks
PN	1-3	4-6	7-9
123	15	12	14
456	22	16	25
789	10	17	12

<tbody>
</tbody>

I need to do this with a formula other than regular formula because PN's are added/subtracted all the time so the order of the PN's changes.
Hopefully that makes sense. I think it is a form of sum with match but struggling to get it to work.

Thanks!

 

[Eric W]

Maybe something like this?

	A	B	C	D	E	F	G	H	I	J	K
1	PN	Week	1	2	3	4	5	6	7	8	9
2	123	Forecast	3	6	6	2	3	7	10	2	2
3	123	Sales	5	6	8	3	1	2	10	6	6
4	123	Returns	6	4	8	10	2	2	7	4	4
5	456	Forecast	8	6	8	9	3	4	7	9	9
6	456	Sales	3	9	3	10	4	8	10	4	4
7	456	Returns	1	1	2	1	6	10	1	2	2
8	789	Forecast	7	2	1	1	10	6	2	5	5
9	789	Sales	2	8	1	6	2	1	10	10	10
10	789	Returns	4	10	7	5	2	4	9	7	7
11
12
13
14
15	Forecast	Weeks
16	PN	1-3	4-6	7-9
17	123	15	12	14
18	456	22	16	25
19	789	10	17	12

<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 B17 =SUMPRODUCT($C$2:$K$10*($A$2:$A$10=$A17)*($B$2:$B$10=$A$15)*($C$1:$K$1>=LEFT(B$16,FIND("-",B$16)-1)+0)*($C$1:$K$1<=MID(B$16,FIND("-",B$16)+1,9)+0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[EdE]

Thanks Eric.
Is this part of the formula finding which columns to sum?
*($C$1:$K$1>=LEFT(B$16,FIND("-",B$16)-1)+0)*($C$1:$K$1<=MID(B$16,FIND("-",B$16)+1,9)+0))

How would I replace with if it is the same columns everytime? IE, weeks 1-3 are always C through E.

Thanks!!

 

[Eric W]

Yes, that finds the columns to sum. It uses the LEFT(FIND("-" part to split the "1-3" into a 1 and a 3 and then compares those with the headings in C1:K1. In order to enter 1-3 you have to use '1-3 because of Excel's penchant for converting 1-3 into a date.

If you use the same columns every time, the easiest way is just to adjust the first range to the columns you want to sum, like this:

	A	B	C	D	E	F	G	H	I	J	K
1	PN	Week	1	2	3	4	5	6	7	8	9
2	123	Forecast	3	6	6	2	3	7	10	2	2
3	123	Sales	5	6	8	3	1	2	10	6	6
4	123	Returns	6	4	8	10	2	2	7	4	4
5	456	Forecast	8	6	8	9	3	4	7	9	9
6	456	Sales	3	9	3	10	4	8	10	4	4
7	456	Returns	1	1	2	1	6	10	1	2	2
8	789	Forecast	7	2	1	1	10	6	2	5	5
9	789	Sales	2	8	1	6	2	1	10	10	10
10	789	Returns	4	10	7	5	2	4	9	7	7
11
12
13
14
15	Forecast	Weeks				Forecast	Weeks
16	PN	1-3	4-6	7-9		PN	1-3	4-6	7-9
17	123	15	12	14		123	15	12	14
18	456	22	16	25		456	22	16	25
19	789	10	17	12		789	10	17	12

<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 B17 =SUMPRODUCT($C$2:$E$10*($A$2:$A$10=$A17)*($B$2:$B$10=$A$15)) C17 =SUMPRODUCT($F$2:$H$10*($A$2:$A$10=$A17)*($B$2:$B$10=$A$15)) D17 =SUMPRODUCT($I$2:$K$10*($A$2:$A$10=$A17)*($B$2:$B$10=$A$15)) G17 =SUMPRODUCT(OFFSET($C$2:$E$10,0,3*(COLUMNS($G17:G17)-1))*($A$2:$A$10=$F17)*($B$2:$B$10=$F$15)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formulas in B17:D17 are all slightly different, since they sum different columns. They can all be dragged down. If you still want a formula that can be dragged down and right, you can use the one in G17, which uses OFFSET to find the right range.

 

[Aladin Akyurek]

A small change to your output representation might ease the processing...

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	PN	Week	1	2	3	4	5	6	7	8	9
2​	123	Forecast	3	6	6	2	3	7	10	2	2
3​	123	Sales	5	6	8	3	1	2	10	6	6
4​	123	Returns	6	4	8	10	2	2	7	4	4
5​	456	Forecast	8	6	8	9	3	4	7	9	9
6​	456	Sales	3	9	3	10	4	8	10	4	4
7​	456	Returns	1	1	2	1	6	10	1	2	2
8​	789	Forecast	7	2	1	1	10	6	2	5	5
9​	789	Sales	2	8	1	6	2	1	10	10	10
10​	789	Returns	4	10	7	5	2	4	9	7	7
11​
12​		Weeks
13​	Forecast	1	4	7
14​	PN	3	6	9
15​	123	15	12	14
16​	456	22	16	25
17​	789	10	17	12

In B15 control+shift+enter, not just enter, copy across, and down:

=SUM(IF($C$1:$K$1>=B$13,IF($C$1:$K$1<=B$14,IF($A$2:$A$10=$A15,IF($B$2:$B$10=$A$13,$C$2:$K$10)))))