Formula to subtotal monthly expense
 

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

Formula to subtotal monthly expense
 

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

"Kent" wrote in message
...
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

Formula to subtotal monthly expense
 

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!

--------------------
(MS-Exl-Learner)
--------------------

"Kent" wrote:

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

Formula to subtotal monthly expense
 

Yes! thank you very much

Kent



"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!

--------------------
(MS-Exl-Learner)
--------------------

"Kent" wrote:

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

Formula to subtotal monthly expense
 

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

"Kent" wrote in message
...
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

Formula to subtotal monthly expense
 

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

"Kent" wrote in message
...
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 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

"Kent" wrote in message
...
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