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

 

[Peter_SSs]

had to change 3 of the , to ;

You might need to change all 4 of the , to ; since I assume that your decimal separator is ","
However, I still think the formula is a bit 'risky' in that if ever rows are inserted/deleted at the top of either of the other two sheets, the formulas will, without warning or error, return incorrect results.

Here is another approach that you could consider.

Jojo86_1.xlsm
	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	=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))))

BTW, are there any row labels in '21 SUP' and '31 CMSW' (maybe in column A?) that could help identify which rows to add, rather than relying solely on the add two, skip 3 idea?

 

[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.

 

[Peter_SSs]

The first one is Subcontractors and the other is Travel.

If that means rows 11, 16, 21, 26 etc is "Subcontractors" and rows 12, 17, 22, 27 etc is "Travel" like below, then assuming those are in column A of the two other sheets, try this which to me is a much better/safer way to approach the task.

Jojo86_1.xlsm
	A																							X	Y
10																								Jun-22	Jul-22
11	Subcontractors																							1	22
12	Travel																							2	54
13
14
15
16	Subcontractors																							23	77
17	Travel																							11	76
21 SUP

Jojo86_1.xlsm
	A																							X	Y
10																								Jun-22	Jul-22
11	Subcontractors																							10	10
12	Travel																							12	22
13
14
15
16	Subcontractors																							44	33
17	Travel																							4	4
31 CMSW

Jojo86_1.xlsm
	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(SUMIF('21 SUP'!$A$11:$A$77,{"Subcontractors","Travel"},'21 SUP'!X11:X77))
B3:C3	B3	=SUM(SUMIF('31 CMSW'!$A$11:$A$77,{"Subcontractors","Travel"},'31 CMSW'!X11:X77))

 

[Jojo86]

Perfect. Works well too. Thanks a lot

 

[Peter_SSs]

You're very welcome. Thanks for the confirmation.