Wednesday, December 3, 2014

Calculate Quarterly totals from monthly data using formula

Calculate Quarterly totals from monthly data!

You have Months Jan to Dec in Column A & against the same in column B you have Revenue figure, now lets see how we can calculate Quarterly total of revenue using sumproduct.

Formula is:
For Q1 =SUMPRODUCT((ROUNDUP(MONTH($A$2:$A$13)/3,0)=1)*$B$2:$B$13)

For Q2 =SUMPRODUCT((ROUNDUP(MONTH($A$2:$A$13)/3,0)=2)*$B$2:$B$13)

And so on..