sum every 6th line from another column

[MAP77]

Hi there,

I need to convert my 5 min interval data into 30 mins by adding up all the 5 min values in the 30 min time slot.
Eg. The 6 highlighted values in column D need to sum in column J. Note: I will add up the values for column D2 to D6 myself as its not a complete 30 mins.

 

[steve the fish]

Try:

=SUM(INDEX(D:D,6*ROWS($A$1:A1)+1):INDEX(D:D,6*ROWS($A$1:A1)+6))

 

[Peter_SSs]

Provided you are not going to have tens of thousands of these formulas, you could also use the volatile function OFFSET as shown in column J.
Or if you have Excel 365 with the SEQUENCE function you could try the column K (non-volatile) formula.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

20 04 02.xlsm
	D	E	F	G	H	I	J	K
1
2	8
3	7						27	27
4	4						31	31
5	4						38	38
6	8						37	37
7	7						29	29
8	5						14	14
9	5
10	2
11	2
12	6
13	3
14	6
15	6
16	5
17	9
18	2
19	2
20	9
21	8
22	4
23	7
24	8
Sum Groups of 6

Cell Formulas
Range	Formula
J3:J8	J3	=SUM(OFFSET(D$1,ROWS(J$3:J3)*6,,6))
K3:K8	K3	=SUM(INDEX(D$2:D$42,SEQUENCE(6,,ROWS(K$3:K3)*6)))

 

[MAP77]

Try:

=SUM(INDEX(D:D,6*ROWS($A$1:A1)+1):INDEX(D:D,6*ROWS($A$1:A1)+6))

Thanks for your help! Just wondering why $A$1:A1 for the row reference?

 

[MAP77]

Provided you are not going to have tens of thousands of these formulas, you could also use the volatile function OFFSET as shown in column J.
Or if you have Excel 365 with the SEQUENCE function you could try the column K (non-volatile) formula.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Thanks Peter! Your sequence formula did the trick

 

[Peter_SSs]

Thanks Peter! Your sequence formula did the trick

You're welcome.

Why not update your profile as suggested and then for the next question helpers will know that you have Excel 365?

 

[MAP77]

Hi Peter,
So I have another set of data that I'm trying to sum the 6 lines for but this time I want to start from the top row and have the sequence sum every 6 variable. I've tried using the formula you gave me last time but cant work out what needs to change to make it work.

So F3 = Sum of G3:G8
F4 = Sum of G9:G14
etc.

 

[Peter_SSs]

I have suggested a couple of times that you update your profile so that we know what version of Excel you have and therefore what functions might be best for your circumstances but so far no action.
I would like to know that before considering suggestions this time.

Also, could you provide the sample data with XL2BB to make it easier for helpers by not having to manually type out sample data to test with?

 

[MAP77]

I have suggested a couple of times that you update your profile so that we know what version of Excel you have and therefore what functions might be best for your circumstances but so far no action.
I would like to know that before considering suggestions this time.

Also, could you provide the sample data with XL2BB to make it easier for helpers by not having to manually type out sample data to test with?

Sorry Peter. I had thought I updated this in my preferences. I have Microsoft 365.

Sum every 6th row sample data.xlsx
	A		C			F	G
1	Date and time		Date and time			Consumption	Consumption
2	[dd.MM.yyyy HH:mm]		30 min			kWhrs (30 min)	kWhrs (5 mins)
3	01.01.2020 00:00		01/01/2020 00:00			0.893	0.022
4	01.01.2020 00:05		01/01/2020 00:30			0.102	0.201
5	01.01.2020 00:10		01/01/2020 01:00			0.162	0.322
6	01.01.2020 00:15		01/01/2020 01:30				0.318
7	01.01.2020 00:20		01/01/2020 02:00				0.01
8	01.01.2020 00:25		01/01/2020 02:30				0.02
9	01.01.2020 00:30		01/01/2020 03:00				0.02
10	01.01.2020 00:35		01/01/2020 03:30				0.02
11	01.01.2020 00:40		01/01/2020 04:00				0.022
12	01.01.2020 00:45		01/01/2020 04:30				0.018
13	01.01.2020 00:50		01/01/2020 05:00				0.01
14	01.01.2020 00:55		01/01/2020 05:30				0.012
15	01.01.2020 01:00		01/01/2020 06:00				0.013
16	01.01.2020 01:05		01/01/2020 06:30				0.019
17	01.01.2020 01:10		01/01/2020 07:00				0.022
18	01.01.2020 01:15		01/01/2020 07:30				0.04
19	01.01.2020 01:20		01/01/2020 08:00				0.041
20	01.01.2020 01:25		01/01/2020 08:30				0.027
21	01.01.2020 01:30		01/01/2020 09:00				0.013
22	01.01.2020 01:35		01/01/2020 09:30				0.023
23	01.01.2020 01:40		01/01/2020 10:00				0.021
24	01.01.2020 01:45		01/01/2020 10:30				0.02
25	01.01.2020 01:50		01/01/2020 11:00				0.023
26	01.01.2020 01:55		01/01/2020 11:30				0.02
27	01.01.2020 02:00		01/01/2020 12:00				0.021
28	01.01.2020 02:05		01/01/2020 12:30				0.022
29	01.01.2020 02:10		01/01/2020 13:00				0.02
30	01.01.2020 02:15		01/01/2020 13:30				0.012
31	01.01.2020 02:20		01/01/2020 14:00				0.011
32	01.01.2020 02:25		01/01/2020 14:30				0.009
33	01.01.2020 02:30		01/01/2020 15:00				0.021
sample data

Cell Formulas
Range	Formula
C3:C33	C3	=TEXT(B3,"dd/mm/yyyy")&" "&TEXT(B3,"hh:mm")
F3	F3	=SUM(INDEX(G$3:G$52418,SEQUENCE(6,,ROWS(F$3:F3))))
F4	F4	=SUM(G9:G14)
F5	F5	=SUM(G15:G20)
G3:G33	G3	=E3/1000

 

[Peter_SSs]

Thanks for the profile update - & the XL2BB sample data!

One way would be to slightly modify your F3 formula to the following and copy down

=SUM(INDEX(G$3:G$52418,SEQUENCE(6,,ROWS(F$3:F3)*6-5)))