» Summing Annual Expenses by Respective Quarters
CATEGORY - Summing
VERSION - All Microsoft Excel Versions
The range A2:B9 contains a list of dates with corresponding expenses.
We want to create a total of the expenses paid during each quarter.
Solution:
Use the SUM, ROUNDUP, and MONTH functions as shown in the following Array formula:
{=SUM((C2=ROUNDUP(MONTH($A$2:$A$9)/3,0))*$B$2:$B$9)}
Book Store:
Recommended Books:
Reply: Saurabh
Alan
Hi Saurabh,
[QUOTE=Saurabh]Hello !
Can any one help me to solve my problem ?
I have G column for date and F column for mode of payment i.e. Y(yearly),H,(Half Yearly),Q(Quarerly). and M column is due date. i want due date calculations according to Y,H,and Q.
plz help.
thanx[/QUOTE]See reply to your other post.
Please don't multi-post - once is enough.
Thanks,
Alan.
Summing Annual Expenses by Respective Quarters
danieldasari1
{=SUM((C2=ROUNDUP(MONTH($A$2:$A$9)/3,0))*$B$2:$B$9)}
Above formula is not working at all, please advice whether the formula is correct or not.
Date Expenses Quarter Result
02/01/2008 100 1 100
09/05/2008 200 2 3101
12/09/2008 200 3 3101
03/10/2008 500 4 3101
08/12/2008 1000
31/01/2008 300
06/04/2008 700
21/02/2008 100
daddylonglegs
The formula should work fine but it's an "array formula" and, as such, needs to be confirmed with CTRL+SHIFT+ENTER
To do this select cell with formula, e.g. D2, press F2 key and then hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar.
...or use a "regular" formula, e.g.
=SUMPRODUCT(--(C2=ROUNDUP(MONTH($A$2:$A$9)/3,0)),$B$2:$B$9)
Can any one help me to solve my problem ?
I have G column for date and F column for mode of payment i.e. Y(yearly),H,(Half Yearly),Q(Quarerly). and M column is due date. i want due date calculations according to Y,H,and Q.
plz help.
thanx