Sum values quickly

[EvansB2]

Hi there,

I quite often have spreadsheets with financial data that are presented in a quarterly fashion. I then need to put them into an annual value and have to date being doing manually! Sometimes, the period of analysis/forecast is 20 yrs, some 80 quarters. In the example below, you can see that cell A7 would sum cells B3:E3 for four the quarters in 2018. I would then like to simply drag a formula along to the end of the period of analysis, capturing the relevant quarters in the relevant year. I think it may require an offset formula?

Any help would be gratefully received.

Regards, Ben

	A	B	C	D	E	F	G	H	I	J	K	L
1	2018	2019	2020
2	QTR 1	QTR 2	QTR 3	QTR 4	QTR 1	QTR 2	QTR 3	QTR 4	QTR 1	QTR 2	QTR 3	QTR 4
3	250	300	200	400	600	350	200	500	150	200	300	700
4
5
6	2018	2019	2020
7	1150	1650	1350

<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="64" style="width: 48pt;" span="12"> <tbody> </tbody>

 

[sheetspread]

=sum(offset($a$3:$d$3,,(column(a1)*4)-4))

 

[EvansB2]

Just had my tea and had a crack at this and works perfectly. Thanks for your help on this.

Ben

 

[Jonmo1]

It may be simpler and more efficient if you could fill in Row 1, so that every cell has the appropriate year number.
Then use
=SUMIF($A$1:$L$1,A$6,$A3:$L3)

 

[EvansB2]

Yes I can do that, though would mean some additional tinkering with the original data and that would mean having to copy and past to a separate sheet because of the way the financial models work.

Thanks for the coming back.


Ben