Sum values quickly

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
226
Office Version
365
Platform
Windows
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

ABCDEFGHIJKL
1201820192020
2QTR 1QTR 2QTR 3QTR 4QTR 1QTR 2QTR 3QTR 4QTR 1QTR 2QTR 3QTR 4
3250300200400600350200500150200300700
4
5
6201820192020
7115016501350
<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>
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

EvansB2

Board Regular
Joined
Nov 25, 2008
Messages
226
Office Version
365
Platform
Windows
Just had my tea and had a crack at this and works perfectly. Thanks for your help on this.

Ben
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Nov 25, 2008
Messages
226
Office Version
365
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,113
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top