Formula for adding cells (with a row interval)

[Jojo86]

Hi,

Some months ago I had a similar question and it was solved here: Formula for adding cells (with a 5 row interval)
using this formula: =SUM(IF(MOD(ROW('21 SUP'!A15:A100),5)=0,'21 SUP'!A15:A100,0))

My question this time is this:

I have a lot of columns and in each column I have to add several cells (with a 1 row interval and a 4 row internal):

Example:
Column A1 ='21 SUP'!A1+'21 SUP'!A5+'21 SUP'!A6+'21 SUP'!A10+'21 SUP'!A11+'21 SUP'!A15+'21 SUP'!A16 etc
Column B1 ='21 SUP'!B1+'21 SUP'!B5+'21 SUP'!B6+'21 SUP'!B10+'21 SUP'!B11+'21 SUP'!B15+'21 SUP'!B16 etc

-> Can you please give me a formula I can use that can be used in different columns?

Thanks everyone

 

[Anonymous1378]

Try this?

=SUM(IF(MOD(ROW('21 SUP'!A1:A100),5)={0,1},'21 SUP'!A1:A100,0))

 

[Peter_SSs]

I would suggest adding some insurance so that if any rows were subsequently added at the top of '21 SUP' the formula would still add the same values.

=SUM(IF(MOD(SEQUENCE(100),5)={0,1},'21 SUP'!A2:A101,0))

 

[Jojo86]

Thanks everyone. I changed the , to ; however I'm getting 0 as a result. Am i doing something wrong?

 

[Peter_SSs]

Could you give us some small sample data and expected results with XL2BB?

 

[Jojo86]

Could you give us some small sample data and expected results with XL2BB?

I can't even install XL2BB "The file type is not supported in Protected View"

I'll get back to this when i have more time. Thanks all.

 

[Peter_SSs]

I can't even install XL2BB "The file type is not supported in Protected View"

I suggest that you review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page

 

[Jojo86]

Ok I managed

First one is what I want fixed.
Second and third are where the data is from.

Thanks

Book1
	A	B	C
1		June 2022	July 2022
2	21 SUP	37	229
3	31 CMSW	70	69
Help

Cell Formulas
Range	Formula
B2:C2	B2	='21 SUP'!X11+'21 SUP'!X12+'21 SUP'!X16+'21 SUP'!X17+'21 SUP'!X21+'21 SUP'!X22+'21 SUP'!X26+'21 SUP'!X27+'21 SUP'!X31+'21 SUP'!X32+'21 SUP'!X36+'21 SUP'!X37+'21 SUP'!X41+'21 SUP'!X42+'21 SUP'!X46+'21 SUP'!X47+'21 SUP'!X51+'21 SUP'!X52+'21 SUP'!X56+'21 SUP'!X57+'21 SUP'!X61+'21 SUP'!X62+'21 SUP'!X66+'21 SUP'!X67+'21 SUP'!X71+'21 SUP'!X72+'21 SUP'!X76+'21 SUP'!X77
B3:C3	B3	='31 CMSW'!X11+'31 CMSW'!X12+'31 CMSW'!X16+'31 CMSW'!X17+'31 CMSW'!X21+'31 CMSW'!X22+'31 CMSW'!X26+'31 CMSW'!X27+'31 CMSW'!X31+'31 CMSW'!X32+'31 CMSW'!X36+'31 CMSW'!X37+'31 CMSW'!X41+'31 CMSW'!X42+'31 CMSW'!X46+'31 CMSW'!X47+'31 CMSW'!X51+'31 CMSW'!X52+'31 CMSW'!X56+'31 CMSW'!X57+'31 CMSW'!X61+'31 CMSW'!X62+'31 CMSW'!X66+'31 CMSW'!X67+'31 CMSW'!X71+'31 CMSW'!X72+'31 CMSW'!X76+'31 CMSW'!X77

Book1
	X	Y
10	June 2022	July 2022
11	1	22
12	2	54
13
14
15
16	23	77
17	11	76
21 SUP

Book1
	X	Y
10	June 2022	July 2022
11	10	10
12	12	22
13
14
15
16	44	33
17	4	4
31 CMSW

 

[Anonymous1378]

As per my earlier suggestion:

Book1
	A	B	C
1		Jun-22	Jul-22
2	21 SUP	37	229
3	31 CMSW	70	69
Help

Cell Formulas
Range	Formula
B2:C2	B2	=SUM(IF(MOD(ROW('21 SUP'!X10:X100),5)={1,2},'21 SUP'!X10:X100,0))
B3:C3	B3	=SUM(IF(MOD(ROW('31 CMSW'!X10:X100),5)={1,2},'31 CMSW'!X10:X100,0))

 

[Jojo86]

I pasted it. And had to change 3 of the , to ; and I get 0 as a result

Mr.Excel (2).xlsx
	A	B	C
1		June 2022	July 2022
2	21 SUP	0	0
3	31 CMSW	0	0
Help

Cell Formulas
Range	Formula
B2:C2	B2	=SUM(IF(MOD(ROW('21 SUP'!X10:X100),5)={1.2},'21 SUP'!X10:X100,0))
B3:C3	B3	=SUM(IF(MOD(ROW('31 CMSW'!X10:X100),5)={1.2},'31 CMSW'!X10:X100,0))

And this shows the formula i have as when i pasted the above {1,2} is showing as {1.2}