Thread: Summing based on a quarterly date criteria.

Problem:

Listed in range A2:B9 are dates and expenses.
How could we total the expenses paid during each quarter in column D?

Solution:

Use the ROUNDUP and MONTH functions to find the dates included in each quarter and sum their matching expenses.
Following is the formula (Array Formula):
{=SUM((C2=ROUNDUP(MONTH($A$2:$A$9)/3,0))*$B$2:$B$9)}

Example:

Date__________Expense____Quarter_____Total Expenses
05/01/2004____200________1___________200
20/03/2004____150________2___________430
11/04/2004____30_________3___________255
11/06/2004____400________4___________200
22/08/2004____35
16/09/2004____220
02/11/2004____120
03/12/2004____80

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

Hi Saurabh,

Originally Posted by 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

See reply to your other post.

Please don't multi-post - once is enough.

Thanks,

Alan.

{=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

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)