I was speaking to a friend earlier who showed me a new way to automatically display quarterly totals using Sum Product.
This Table is set up in A2:D14
This Table is set up in A2:D14

=SUMPRODUCT((ROUNDUP(MONTH(A3:A14)/3,0)=2)*(C3:C14))

Now this function has the same effect as simply inputting =SUM(C3:C5) but has the advantage that you can have the same formula in each cell, you select which Quarter you want to return by adjusting the =2 to show which quarter you require (i.e Jan - March is 1, Apr - Jun is 2 etc...)
I think perhaps that the best use for this however is to discourage the occasional excel user from trying to "Improve" your worksheets.
No comments:
Post a Comment