Formula to subtotal monthly expense

  • Thread starter Thread starter Kent
  • Start date Start date
K

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
 
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
 
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!
 
Yes! thank you very much

Kent



Ms-Exl-Learner said:
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!
Click to expand...
 
Dear Ashish,

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

Kent
 
Hi,

you may also use this formula. C9:C15 (excluding the header) contains
dates, D9:D15 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
 
Back
Top