Formula to add 3 horizontal monthly values and create quarterly values below

[Captain Hindsight]

Hi,

I have a data series running horizontally, which gives me monthly values e.g.

Jan - £10 Feb £12 Mar £11 etc. Imagine the months are in row 2 beginning from column 2, and the values are in column 3.

I would like a formula which adds the three values, and in row 6 column two gives me the total for the quarter (10 + 12 + 11) and displays 33.
The next quarter value, I would like in row 6 column 3, etc.

Any help is much appreicated,
thanks!

 

[Andrew Poulsom]

Example (formula in B6 copied across):

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Sales	10	12	11	13	14	15	16	17	18	19	20	21
4
5		Q1	Q2	Q3	Q4
6	Sales	33	42	51	60
Sheet4

Cell Formulas
Range	Formula
B6	=SUM(OFFSET($B$3,0,(COLUMNS($B6:B6)-1)*3,1,3))

 

[Captain Hindsight]

Also please note my dates are in the format:

Jan-14, Feb-14, Mar-14

 

[Andrew Poulsom]

Is that relevant?

 

[Captain Hindsight]

Example (formula in B6 copied across):

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Sales
Q1	Q2	Q3	Q4
Sales

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet4​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]B6[/TH]
[TD="align: left"]=SUM(OFFSET($B$3,0,(COLUMNS($B6:B6)-1)*3,1,3))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

This works perfectly.

Thank you very much for your quick response!

 

[Captain Hindsight]

Is that relevant?

With your formula, no.

I posted that before I saw your response but thought it could have had relevance.