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

[TABLE="width: 598"]
<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="64" style="width: 48pt;" span="12"> <tbody>[TR]
[TD="class: xl63, width: 29, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]A[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]B[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]C[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]D[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]E[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]F[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]G[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]H[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]I[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]J[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]K[/TD]
[TD="class: xl64, width: 64, bgcolor: #D9D9D9"]L[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]1[/TD]
[TD="class: xl71, bgcolor: transparent, colspan: 4"]2018[/TD]
[TD="class: xl71, bgcolor: transparent, colspan: 4"]2019[/TD]
[TD="class: xl71, bgcolor: transparent, colspan: 4"]2020[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]2[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 1[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 2[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 3[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 4[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 1[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 2[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 3[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 4[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 1[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 2[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 3[/TD]
[TD="class: xl63, bgcolor: transparent"]QTR 4[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]3[/TD]
[TD="class: xl63, bgcolor: transparent"]250[/TD]
[TD="class: xl63, bgcolor: transparent"]300[/TD]
[TD="class: xl63, bgcolor: transparent"]200[/TD]
[TD="class: xl63, bgcolor: transparent"]400[/TD]
[TD="class: xl63, bgcolor: transparent"]600[/TD]
[TD="class: xl63, bgcolor: transparent"]350[/TD]
[TD="class: xl63, bgcolor: transparent"]200[/TD]
[TD="class: xl63, bgcolor: transparent"]500[/TD]
[TD="class: xl63, bgcolor: transparent"]150[/TD]
[TD="class: xl63, bgcolor: transparent"]200[/TD]
[TD="class: xl63, bgcolor: transparent"]300[/TD]
[TD="class: xl63, bgcolor: transparent"]700[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]4[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]5[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]6[/TD]
[TD="class: xl69, bgcolor: transparent"]2018[/TD]
[TD="class: xl69, bgcolor: transparent"]2019[/TD]
[TD="class: xl69, bgcolor: transparent"]2020[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #D9D9D9"]7[/TD]
[TD="class: xl70, bgcolor: transparent"]1150[/TD]
[TD="class: xl70, bgcolor: transparent"]1650[/TD]
[TD="class: xl70, bgcolor: transparent"]1350[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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