| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Does anyone have a Monthly Expense Calendar Spreadsheet template? | Excel Discussion (Misc queries) | |||
| Producing expense (monthly) reports in Excel | Excel Discussion (Misc queries) | |||
| home monthly expense report | Excel Discussion (Misc queries) | |||
| How do I set up a monthly business expense sheet | New Users to Excel | |||
| how do i make a monthly expense spread sheet | Excel Discussion (Misc queries) | |||
Hybrid Mode
