How to do this ?

[Oliver Marshall]

Hi,

I have a list of expenses in a spreadsheet as so;

date--------description---------amount
1/1/06 entertainment £28
3/1/06 fuel £50

etc

The dates are no real order as i enter them as i turf the receipts out
of my car. I want to create a table that shows a breakdown the
descriptions by month (theres only really 5; Fuel, Entertainment, Car
etc). Should look like this;

January
Car £28
Fuel £50
Entertainment £100
February
Car £40

etc

Anyone know how I can do this easily from the list I have ? the dates
are in dd/mm/yy format.

Olly

 

[Nick Hodge]

Oliver

This is a perfect format for a pivot table

Check here

http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk


Hi,

I have a list of expenses in a spreadsheet as so;

date--------description---------amount
1/1/06 entertainment £28
3/1/06 fuel £50

etc

The dates are no real order as i enter them as i turf the receipts out
of my car. I want to create a table that shows a breakdown the
descriptions by month (theres only really 5; Fuel, Entertainment, Car
etc). Should look like this;

January
Car £28
Fuel £50
Entertainment £100
February
Car £40

etc

Anyone know how I can do this easily from the list I have ? the dates
are in dd/mm/yy format.

Olly

 

[Don Guillett]

suggest the 5 categories along the top row and the months down the columns
Jan
Feb
Mar
Then just use a sumproduct formula
=sumproduct((month(daterng)=row(a1))*(catrng=cat)*expenserng)
and copy down
--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi,

I have a list of expenses in a spreadsheet as so;

date--------description---------amount
1/1/06 entertainment £28
3/1/06 fuel £50

etc

The dates are no real order as i enter them as i turf the receipts out
of my car. I want to create a table that shows a breakdown the
descriptions by month (theres only really 5; Fuel, Entertainment, Car
etc). Should look like this;

January
Car £28
Fuel £50
Entertainment £100
February
Car £40

etc

Anyone know how I can do this easily from the list I have ? the dates
are in dd/mm/yy format.

Olly

 

[Bernard Liengme]

I will assume the dates to be in A2:A400, items in B2:B400, amount in
C2:C400 (row 1 having headers)
In F1:J1 enter the names of the items - Fuel, Entertainment, Car, etc
In E2 enter 1/1/2007 and in E3 enter 1/2/2007
Selects these two cells and pull the fill handle down to E13 giving you 12
dates - the first of each month
Format these with custom format mmmm; now you see the names of 12 months
In F2 enter this formula (correct the ranges as needed)
=SUMPRODUCT(--(MONTH($A$2:$A$400)=MONTH($E2)),--($B$2:$B$400=F$1),$C$2:$C$400)
Copy this across to J2, then copy E2:J2 down to row 13
Now you have a nice table like this
Fuel Entertain Rent Food Etc
January 475 136 286 303 231
February 335 228 514 522 645
March 466 761 250 629 698
April 446 448 452 412 352


best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Hi,

I have a list of expenses in a spreadsheet as so;

date--------description---------amount
1/1/06 entertainment £28
3/1/06 fuel £50

etc

The dates are no real order as i enter them as i turf the receipts out
of my car. I want to create a table that shows a breakdown the
descriptions by month (theres only really 5; Fuel, Entertainment, Car
etc). Should look like this;

January
Car £28
Fuel £50
Entertainment £100
February
Car £40

etc

Anyone know how I can do this easily from the list I have ? the dates
are in dd/mm/yy format.

Olly