Annual SUMPRODUCT from decades of data

[ReEngineer]

I have data in column B and months in column A and number of days in column C. I have to sumproduct annual data in the following way.
Multiply production for a particular month with the number of days in that month (it would be nice to somehow incorporate the number of days in month rather than manually calculating them) and sum this for for 2012 and then divide it by the number of days in that year. This gives me an annualized data. I want to do this for 2013, 2014 and so on without manually typing in the formula as it becomes too tedious.

Annual production
2012 =SUMPRODUCT(B1:B12,C1:C12)/SUM(C1:C12)
2013 =SUMPRODUCT(B13:B24,C13:C24)/SUM(C13:C24)

 

[Aladin Akyurek]

Where do you have the year information?

 

[dmadeka]

I have arranged the sheet as follows - change formulas to match your data

Row 1 - Headers

Row 2 onwards
Col A
Date (Month and Year)
Col B
Data
Col C
No of days in a month Formula =DAY(EOMONTH(A2,0))

Col E
Year - for your report
Col F
=SUMPRODUCT(--(YEAR($A$2:$A$500)=E2),$B$2:$B$500,$C$2:$C$500)/SUMPRODUCT(--(YEAR($A$2:$A$500)=E2),$C$2:$C$500)

The formula assumes you have data till row 500 extend as necessary

hope it helps

 

[ReEngineer]

@dmadeka
Thanks... the formula works great.. although I didn't understand how?

 

[dmadeka]

It would need a long post to explain exactly. But simply put the YEAR($A$2:$A$500)=E2 creates an array which has TRUE if the year is equal to the report year and FALSE otherwise.

The double negation "--" converts the TRUE to 1 and FALSE to 0. Then the sumproduct just multiplies the arrays as usual.

A really nice explaination of what is possible with sumproduct given by Daniel Ferry LINK:

 

[dmadeka]

The link doesnt seem to work.

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html