daily tracking of expenses

[ZeRo]

We're trying to keep track of our budget expenses, so i wrote up a
spreadsheet to calculate how much were spending on each item. Everytime we
make a transaction i'll enter it on a new row in excel, but have a number of
colums to put the cost of what we spent.... ie. groceries, bills, fun..
etc.... then on the right, the totals for each one....
what i'm asking is, is it somehow possible to have only one column beside
what we spent money on and put a code letter in it to specify what it was
for... G for groceries, F for fun etc.... then have a formula to calculate
the sum of all the rows with F as the code letter... just to eliminate having
7+ columns taking up space...

here's a pic of my spreadsheet
http://ca.pg.photos.yahoo.com/ph/zerogod33/album?.dir=/3e05scd&.src=ph&.tok=phw2ydFBKXmZ4Awd

any help would be greatly appreciated.... thank you....

 

[Gilbert]

You could enter the code in one column and the amount in another column.
When you are done entering your budget create a pivot table or a simple sumif
function.

Ano

 

[ZeRo]

well... its more of an expense tracker... i know i could easily sum up all
the cells at the end into each category that i wanted. i want to view the
total dollars i've spent as i input each transaction... lets say 1/2 way
through the month i look over to the right and see that my total fun spending
is already up to what i budgeted for the month..... and groceries are doubled
what i wanted.... rather then at the end of the month calculate and say , "oh
shit, i spent more money that i had" ..

 

[ZeRo]

nevermind... i searched up sumif formula's in google and figured out what i
wanted to do.. i didn't know sumif existed until you mentioned them
Gilbert... thanks alot.....

 

[Roger Govier]

Hi
What Gilbert is suggesting would do just that.
If you don't want to set up a Pivot Table and you have just 7
categories, insert 3 rows above your Data.
In Cell A1 type Fun, B1 Groceries, etc until you have your categories
listed.
In A2 enter |F, in B2 enter G etc., as long as each letter is unique

Assuming you are entering your data with Date in column A, Category in B
and Amount in C then
in cell A3 enter
=SUMIF($A$4:$A$1000,A$2)
Copy this formula across through cells B2:G2

Place your cursor in cell A4 and select Windows>Freeze Panes

Now as you enter data down the page you will see the totals by category
appearing in row 3

 

[ed]

We're trying to keep track of our budget expenses, so i wrote up a
spreadsheet to calculate how much were spending on each item. Everytime we
make a transaction i'll enter it on a new row in excel, but have a number of
colums to put the cost of what we spent.... ie. groceries, bills, fun..
etc.... then on the right, the totals for each one....
what i'm asking is, is it somehow possible to have only one column beside
what we spent money on and put a code letter in it to specify what it was
for... G for groceries, F for fun etc.... then have a formula to calculate
the sum of all the rows with F as the code letter... just to eliminate having
7+ columns taking up space...

here's a pic of my spreadsheet
http://ca.pg.photos.yahoo.com/ph/zerogod33/album?.dir=/3e05scd&.src=ph&.tok=phw2ydFBKXmZ4Awd
any help would be greatly appreciated.... thank you....

Congratulation, you just reinvented the wheel. Quicken and Money do this, but yours does what you want. Instead of Goggleing "sumif" you should get a good text on Excel to see how powerful it is. also, look into Quicken.

ed

 

[ed]

We're trying to keep track of our budget expenses, so i wrote up a
spreadsheet to calculate how much were spending on each item. Everytime we
make a transaction i'll enter it on a new row in excel, but have a number of
colums to put the cost of what we spent.... ie. groceries, bills, fun..
etc.... then on the right, the totals for each one....
what i'm asking is, is it somehow possible to have only one column beside
what we spent money on and put a code letter in it to specify what it was
for... G for groceries, F for fun etc.... then have a formula to calculate
the sum of all the rows with F as the code letter... just to eliminate having
7+ columns taking up space...

here's a pic of my spreadsheet
http://ca.pg.photos.yahoo.com/ph/zerogod33/album?.dir=/3e05scd&.src=ph&.tok=phw2ydFBKXmZ4Awd
any help would be greatly appreciated.... thank you....

Congratulation, you just reinvented the wheel. Quicken and Money do this, but yours does what you want. Instead of Goggleing "sumif" you should get a good text on Excel to see how powerful it is. also, look into Quicken.

ed

 

[ZeRo]

thank you soo much for the help guys..... saved me lots of time.....

i basically did the same thing that Roger said... date in A, Item in B, Code
in C, Amount in D

then in columns G and H my totals.

my formula was , =SUMIF(C:C,I5,D) The I column is just where i put the
code letter for reference.

it worked out the same... summing up all the dollar amounts in the D column
that had the specified Code letter in the C column...

one question though... what's the $$ signs for in the formula you equated??

Also, here is a pic of what i came up with for my end result if anyone wants
to check it out..

http://ca.pg.photos.yahoo.com/ph/zerogod33/detail?.dir=3e05scd&.dnm=6e84re2.jpg&.src=ph

thanks again for all the help.. .

 

[Roger Govier]

Hi

Firstly, I must apologise as I my formula was totally wrong (mental
blockage late at night) - but you figured out the correct solution
anyway.

In answer to your question, the $ signs are used to turn relative cell
reference into absolutes.
This means that they will not alter as you drag or copy a formula across
or down a page.
If in cell A3 you had
=SUMIF(C4:C100,A2)
as you drag across it would become
=SUMIF(D4100,B2), (E4:E5=C2) etc.
Whilst the change in A2 to B2 to C2 is what we ant, the range to be
summed should always be C4:C100

Placing a $ before column letter, locks the column. as you copy across,
but would let the row alter as you copy down
Placing a $ before row number locks the row as you copy down, but would
allow the column letter to alter as you copy across.
Putting $ before column and row locks both

With your cell formula highlighted in the formula bar, pressing F4
repeatedly will cycle the reference through the four options
A1
$A$1
A$1
$A1