Sum months in range without pivot table or array function?

[sengenbe]

After a day and a half of digging, I haven't quite found the answer I am looking for here. I have a large dump of monthly data related to properties which is monthly for 10 years. About 13k rows of accounts and 120 months

I want to quickly sum the months in each year, and since I need to use my output to feed into another model, I cannot use a pivot table due to the iterations it would require. (I would have to copy/paste way too many times)

I tried using the array function but it started freezing once I copied the formula through the first two columns (2010 and 2011), so that option is out. Here's my current array formula: {=SUM((YEAR('Data Dump'!E5:EF5)=Annual!E6)*'Data Dump'!E7:EF7)}

Row 5 in the data dump contains the months (i.e. Jan-2010), Annual E6 is 2010, and row 7 has the revenue data I am trying to sum.

Is there any other way to efficiently sum these? I am not good with VBA but was thinking a macro could do the array function by row and change the 2nd or 3rd previously "sum arrayed" row data to text format or just values. That way once the necessary data is in my annual sheet, the unneccessary array function is removed. Anyone have any hints on how to approach this?

Thanks in advance and let me know if anything is unclear!

 

[Aladin Akyurek]

What happens if you change 2010 in

Annual!E6

to

1-Jan-2010

and enter in

Annual!F6

=EDATE(E6,12)

then change the formula to:

=SUMIF('Data Dump'!E5:EF5,">="&Annual!E6,'Data Dump'!E7:EF7)-
    SUMIF('Data Dump'!E5:EF5,">="&Annual!F6,'Data Dump'!E7:EF7)

 

[Jonmo1]

{=SUM((YEAR('Data Dump'!E5:EF5)=Annual!E6)*'Data Dump'!E7:EF7)}

Here's an equivelent formula

=SUMPRODUCT(--(YEAR('Data Dump'!E5:EF5)=Annual!E6),'Data Dump'!E7:EF7)

But it probably wont' be much faster, it's still technically an array formula.
It just does not require CTRL + SHIFT + ENTER

I don't think a VBA solution would be much faster either.
It's the same amount of math, occupying the same amount of Processer time.
My general rule of thumb is:
"If it CAN be done with a formula, then it probably SHOULD be done with a formula"

The only advantage I can see with a VBA solution, is the calculations would only be done when you run the macro. But with formulas, they recalculate anytime you change a related cell value..


I'm betting Aladin's solution will probably be best, can't go wrong with Aladin..

 

[sengenbe]

Yep, Aladin's answer worked perfectly. Thanks a ton for the quick reply! Saved me hours no doubt.