sumifs increment column down

[kyndig]

Hi all

I've scrubbed the forums, and have tried multiple variations of index/match/offset and can't get this formula to work correctly.

I have a table that will be used to generate a chart, the columns of C,D,E.. list sum'ed up values from another sheet (further below). I'm trying to copy the formula down and increment the columns for the sum_range. To manually increment the columns down would be really tasking "weekly_table!G:G ..weekly_table!H:H...etc". Here is a snapshot of the table I'm trying to make for my chart:

Excel Workbook
	A	B	C	D
10	WEEKNUMBER	FROM	PAX Moved	VIP Missions
11	1	28 Feb - 06 Mar	543	749
12	2	07 Mar - 12 Mar	1433	1961
13	3	14 Mar - 18 Mar	467	617
Sheet

Here is the information from the weekly_table - it is a cross-tab query from access that is dropped into the weekly_table sheet. From column G on it is the 'week' value that I am trying to sum up above. (I'll be doing the same thing with a monthly_table)

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	intCatId	Category_Name	Company	Section	SUBSECTION	1	2	3	4	5	6	7
2	35	ADB Express	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move	60	30	77	44	34	98
3	36	CTU East	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move		30	10	40
4	37	CTU West	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move			67	53	23
5	38	CTZ North	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move			54		86
6	42	K Connect	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move							100
7	43	Other	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move	36	7	23	3	4	22	42
8	44	Pallet/Cargo Weights	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move	749	1961	617	639	285	3379	13
9	45	PAX Weights	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move	718	714	1179	1688	671	712	134
10	47	RIP	Alpha	MCT	RC(S) Expansion/Ressuply/Pax Move	374	1114	7	10	153		450
weekly_table

Thanks gurus.

 

[Aladin Akyurek]

Lets just take this from C11:

=SUMIFS(weekly_table!G:G,weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

How would this look like if you changed it manually when copied down or across?

 

[kyndig]

Lets just take this from C11:

=SUMIFS(weekly_table!G:G,weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

How would this look like if you changed it manually when copied down or across?

Thanks Aladin, I may have overcomplicated the request. When copied down, it would look like:

=SUMIFS(weekly_table!H:H,weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

=SUMIFS(weekly_table!I:I,weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

 

[Aladin Akyurek]

Thanks Aladin, I may have overcomplicated the request. When copied down, it would look like:

=SUMIFS(weekly_table!H:H,weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

=SUMIFS(weekly_table!I:I,weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

In C11 enter and copy down...

=SUMIFS(INDEX(weekly_table!G:Z,0,ROWS($C$11:C11)),
weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

Note the correspondance between C11 and ROWS($C$11:C11).

 

[kyndig]

In C11 enter and copy down...

=SUMIFS(INDEX(weekly_table!G:Z,0,ROWS($C$11:C11)),
weekly_table!$D:$D,"=Alpha",weekly_table!$E:$E,"=MCT",weekly_table!$F:$F,"=RC(S) Expansion/Ressuply/Pax Move",weekly_table!$C:$C,"<>*Weights*",weekly_table!C:C, "<>*VIP*")

Note the correspondance between C11 and ROWS($C$11:C11).

Works like a charm Aladin. Thank you extremely. I did try using the ROWS() method with INDEX as the column value. I had the array referenced incorrectly though. Wow, I should have asked about 40 hours ago.

Much appreciated!

 

[Aladin Akyurek]

Works like a charm Aladin. Thank you extremely. I did try using the ROWS() method with INDEX as the column value. I had the array referenced incorrectly though. Wow, I should have asked about 40 hours ago.

Much appreciated!

You are welcome. Thanks for providing feedback.