categorized daily expenses into SUM UP with monthly basis

Hi,

I track my daily expenses.I want to SUM them into each category with monthly basis. Pls see attached file to get better idea.

Thanks
GP

Hi,

remove the empty row 8, click into the table, then click Data - Pivot Table and Pivot chart... and follow the wizard to create a Pivot table.

Then you don't need any complicated formulae, which will be less efficient and harder to maintain than the pivot table. Read here about some background and tutorials available:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

If your *real* data is structured like your sample workbook,
a pivot table would automatically do what you want:

First, remove any blank rows from your data.
Then...
From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW: Drag the DATE field here
COLUMN: Drag the CATEGORY field here
DATA: Drag the COST field here
If it doesn't list as Sum of COST...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table…Click [Finish].
...almost done...
Right-Click the DATE label on the pivot table
Select: Group and Show Detail.Group...by Month

That will list each intersection of MONTH and CATEGORY and the sum of COST.

To refresh the Pivot Table, just right click it and select Refresh Data

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datam...ablereport.htm
http://www.contextures.com/tiptech.html

Is that something you can work with?

Typically, a data area does not have unlabelled blank rows.
(I hope the blank lines aren't just for asthetics.)

Anyway...using your sample workbook...

Please Login or Register  to view this content.

Copy that formula across and down through H4.

Is that something you can work with?

Hi, in typical accounting balance sheet layout, you would have your months in columns and the cost categories in rows. This will also make the table less wide, since you'll have only 12 months in a year and more cost categories than that. It's easier to scroll down to see more categories than to scroll right. So it might be worthwile transposing your table.

just a thought while you're still fine-tuning ....

There's a sticky thread at the top of the Excel Worksheet Functions forum, with a lot of links to online resources. It is grouped by topic and has sections for beginners. Definitely worth a look.

http://www.excelforum.com/excel-work...additions.html