SumProduct on a variable length using a mask

[Calgary_Neil]

I have been using this formula {which is working fine}

SUMPRODUCT( N( MOD( COLUMN( $I37:AJ37)) -6, 7)= 0), $I37:AJ37) +F37

where the first part creates a mask for every 7 column, Middle part is numbers, text and blanks which only every 7th is needed, and the last part is a offset value. I extend this by inserting columns before Column AJ in batches of 7.

Now I what to do is sum every 7th column from $I to AJ-21 {with AJ increasing with inserted column counts}. I have tried OFFSET , it is dynamic I know, but it returns values which fail in COLUMNs.

Anyone have solution?
Thanks for reading
Neil T

 

[StephenCrump]

Here's one way you could do it (not sure if you want totals column by column, or just a grand total?):

	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	8	1	1	1	1	1	1	3	1	1	1	1	1	1	2	1	1	1	1	1	1	9	1	1	1	1	1	1
2	2	1	1	1	1	1	1	6	1	1	1	1	1	1	2	1	1	1	1	1	1	7	1	1	1	1	1	1
3	4	1	1	1	1	1	1	1	1	1	1	1	1	1	7	1	1	1	1	1	1	3	1	1	1	1	1	1
4
5
6	14	10	11	19
7
8	54
Sheet1

Cell Formulas
Range	Formula
I6:L6	I6	=SUMPRODUCT(INDEX($I1:$AJ3,,1+7*(COLUMNS($I6:I6)-1)))
I8	I8	=SUM(I1:AJ3*NOT(MOD(COLUMN(I1:AJ3)-COLUMN(I1:I3),7)))
Press CTRL+SHIFT+ENTER to enter array formulas.

I suggest you update your signature to let us know which version(s) of Excel you're using - the best solution may vary with version.

 

[Calgary_Neil]

Thanks. Need to play with it, but I don't think it does what I want.

Neil10C2_SE_4X_CE_V2.60.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER
2	17								Week 1							Week 2							Week 3							Week 4							Week 5							Week 6							Week 7							Week 8							Week 9							Week 10							Week 11							Week 12							Week 13							Week 14							Week 15							Week 16							Week 17							Week 18							Week 19							Week 20
3		170										Parts in Stock	25						Parts in Stock	32						Parts in Stock	38						Parts in Stock	54						Parts in Stock	68						Parts in Stock	65						Parts in Stock	74						Parts in Stock	84						Parts in Stock	86						Parts in Stock	81						Parts in Stock	97						Parts in Stock	91						Parts in Stock	96						Parts in Stock	97						Parts in Stock	101						Parts in Stock	124						Parts in Stock	117						Parts in Stock	122						Parts in Stock	124						Parts in Stock	124
4
5		133											25							32							38							54							68							65							74							84							86							81							97							91							96							97							101							124							117							122							124							124
Sheet1

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT( N( MOD( COLUMN( $I3:ER3)-6, 7) =0), $I3:ER3) /10
B5	B5	=SUM(I5:DU5)/10

What I want to do is based on A2, get the value of $I$3 to 9+7*A2 row 3, masking the other cells except every 7th cell starting @ M (13th column). Row 5 shows this in a manual way. Other rows have numbers in different columns and may have numbers in more than 1 column in the set of seven, hence the masking. It's getting the variable length to work with the masking that I think I'm having problems with.

 

[KRice]

Since you have Excel 365, I would consider using SEQUENCE to form the indexing array and use an idea like the one below. Here you can simply change the Number of weeks to extend the indexing array automatically. The few rows below your original ones show how the different components in the main formula work to build the indexing array {1,2,3,...}, how MOD is used to identify only the values of interest, and how SUM then adds those terms (only the yellow cells are needed, the others used for illustration purposes can be deleted). Just make sure the INDEX($I$3:$ZZ$3 part of the formula extends far enough to cover the entire range you want to add (it can be much larger than needed as I've shown here with $ZZ$3).

MrExcel_20220512B.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2	17								Week 1							Week 2							Week 3
3		170										Parts in Stock	25						Parts in Stock	32						Parts in Stock	38
4
5		133											25							32							38
6									1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
7	Sequence starts at	1							0	0	0	0	25	0	0	0	0	0	0	32	0	0	0	0	0	0	38	0	0
8	Number of weeks	20							170
9	Sum/10	170
Sheet2

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT( N( MOD( COLUMN( $I3:ER3)-6, 7) =0), $I3:ER3) /10
B5	B5	=SUM(I5:DU5)/10
M5,AA5,T5	M5	=M3
I6:ER6	I6	=SEQUENCE(1,7*$B$8)
I7:ER7	I7	=INDEX(I3:ZZ3,,I6#*(MOD(I6#,7)=5))
I8	I8	=SUM(INDEX($I$3:$ZZ$3,,I6#*(MOD(I6#,7)=5)))/10
B9	B9	=SUM(INDEX($I$3:$ZZ$3,,SEQUENCE(1,7*$B$8)*(MOD(SEQUENCE(1,7*$B$8),7)=5)))/10
Dynamic array formulas.

 

[Calgary_Neil]

Quick look I don’t see A2 being a variable in adjusting the length. Will this work with INDIRECT (which is also dynamic) or is there a better way?

 

[KRice]

Cell B8 in my example is effectively the same as cell A2 in your example. It establishes how many weeks are involved in the SUM. I wouldn't say that INDIRECT is dynamic...it is volatile, meaning that it needs to execute every time the sheet recalculates. This can lead to slow performance. In the orange version, I changed the cell reference for number of weeks to A2 and shortened the formula slightly by using a LET function to define the redundant indexing array term with a short name that is referenced elsewhere in the main formula. Give the orange cells a try and see if the desired results are produced.

MrExcel_20220512B.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	17								Week 1
3		170										Parts in Stock	25
4
5		133											25
6
7	Sequence starts at	1
8	Number of weeks	17
9	Sum/10	133
10	Sum/10	133
Sheet2 (2)

Cell Formulas
Range	Formula
B3	B3	=SUMPRODUCT( N( MOD( COLUMN( $I3:ER3)-6, 7) =0), $I3:ER3) /10
B5	B5	=SUM(I5:DU5)/10
M5	M5	=M3
B9	B9	=SUM(INDEX($I$3:$ZZ$3,,SEQUENCE(1,7*$B$8)*(MOD(SEQUENCE(1,7*$B$8),7)=5)))/10
B10	B10	=LET(idx,SEQUENCE(1,7*$A$2),SUM(INDEX($I$3:$ZZ$3,,idx*(MOD(idx,7)=5)))/10)

 

[Calgary_Neil]

Thanks. That works, now I have to understand it. The only one new is SEQUENCE, but that is excel always 1 new method of Function to master.

 

[KRice]

I'm happy to help. SEQUENCE is indeed dynamic. It creates an array tailored to your inputs: number of rows, columns, start number, and step size. In this case, it's easy to establish an array {1,2,3,...} to cover all of the cells in your range of interest. Then we do a MOD 7 on the array, and since the MOD 7 of every value in your sum range is 5, we can use INDEX in conjunction with the MOD 7 condition to return only the values of interest from your sum range. Study the illustrative example in my Post #4 to see this (in I6:I7).