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?

 

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

 

[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