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]

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}

 

[Jojo86]

Hei,

I changed all 4 , to ; and got N/A error.

HOWEVER your new formula (below) Peter worked. Thanks

Range	Formula
Cell Formulas
B2:C2	B2	=LET(r,'21 SUP'!X11:X77,s,MOD(SEQUENCE(ROWS(r)),5),SUM(FILTER(r,(s=1)+(s=2))))
B3:C3	B3	=LET(r,'31 CMSW'!X11:X77,s,MOD(SEQUENCE(ROWS(r)),5),SUM(FILTER(r,(s=1)+(s=2))))

I didn't think about the Row Labels (i'm not good in excel). The first one is Subcontractors and the other is Travel. If you would like to share that in a formula too that would be great Thanks.

 

[Jojo86]

Perfect. Works well too. Thanks a lot

 

[Peter_SSs]

You're very welcome. Thanks for the confirmation.