Formula to subtotal monthly expense

[Kent]

Dear all,

I have a worksheet

Date Expense
2/1/09 5000
10/1/09 3000
15/1/09 1500
23/2/09 2500
24/2/09 2400
1/3/09 1500
2/3/09 1000

I now want to subtotal the expenses into a new table month by month

Month Expense
Jan 09
Feb 09
Mar 09

WHat should be the formula in the "Expense" column?

Thank you

Kent

 

[Ashish Mathur]

Hi,

You could work with a pivot table. Drag date to the row area and expense to
the data area. Now click on any one cell in the date column and on the
pivot table toolbar, click on Group and Show Detail > Group. Select Months
(that may be the default selection).

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ms-Exl-Learner]

In C2 put this formula

=TEXT(A2,"MMM")&" "&TEXT(A2,"YY")

and drag it upto C8 and in B11 paste this formula

=SUMIF($C$2:$C$8,TRIM($A11),$B$2:$B$8)

(i.e.) Nearby the Jan 09 cell and drag it to your range.

If this helps, Click Yes!

 

[Kent]

Yes! thank you very much

Kent

In C2 put this formula

=TEXT(A2,"MMM")&" "&TEXT(A2,"YY")

and drag it upto C8 and in B11 paste this formula

=SUMIF($C$2:$C$8,TRIM($A11),$B$2:$B$8)

(i.e.) Nearby the Jan 09 cell and drag it to your range.

If this helps, Click Yes!

 

[Kent]

Dear Ashish,

What I really want is the formula but not a pivot table.
ANyway thank very much for your assistance.

Kent

 

[Ashish Mathur]

Hi,

you may also use this formula. C9:C15 (excluding the header) contains
dates, D915 contains amounts. C18 has Jan 2009. Please note that when
you type in Jan 2009 in a cell, the formula bar displays 1/1/2009

=SUMPRODUCT(($C$9:$C$15>=C18)*($C$9:$C$15<=EOMONTH(C18,0))*($D$9:$D$15))

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com