PDA

View Full Version : excel question



Ron Bakker
27-02-2008, 06:30 AM
I'm working on a cashbook. Copy and pasting my bank statement into a worksheet, leaving a column on the left to categorize expenses. So that when I go through my bank statement and see an expense that is eg "petrol" all I do is type it in and it automatically sums all the petrol labeled cells and put's the result in a cell that Iíve named petrol.
DOes that make sense?

the_bogan
27-02-2008, 06:45 AM
Use the =SUMIF function.

I'm not sure what columns you have or how many rows, but try something like this. (Assuming descriptions are column B and amounts are column C)

=SUMIF(A1:A500,"petrol",B1:B500)

Sorry if this doesn't work, don't have Excel on this pc at the moment, can confirm in just over an hour.

jberries
27-02-2008, 07:42 AM
Heres some random thoughts
-I assume you download the bank statement from your bank, you should be able to download it as a .csv (comma delimited file) or .xls . Then you can load it into excel instead of copy and paste.
-Macros might also be helpful in excel. They are a way for automating common or repetitive tasks. You might want to check the excel help for more info on them.
-MS Money has a way of "learning" common transaction types and next month when you load a statement, it will identify and total the ones it already knows. This might be overkill.

rad_s4
27-02-2008, 10:02 AM
Use the =SUMIF function.

I'm not sure what columns you have or how many rows, but try something like this. (Assuming descriptions are column B and amounts are column C)

=SUMIF(A1:A500,"petrol",B1:B500)

Sorry if this doesn't work, don't have Excel on this pc at the moment, can confirm in just over an hour.

Under these circumstances - the appropriate formula will be

=SUMIF(B1:C500,"petrol",C1:C500) [=SUMIF(Range,Criteria,Sum_Range)]

you don't have to name the cell where the formula is for it to work.

the_bogan
27-02-2008, 01:13 PM
Yeah sorry :blush: , I did mean =SUMIF(B1:B500,"petrol",C1:C500)
Was a bit early in the morning for me.

@ rad_s4.Not sure why you would be wanting to check column C for Petrol?

Had a thought. If he's just referencing a download of his bank file, the description is going to change, depending on where he's used his card etc, isn't it? That would involve having to manually standardise each description.

Ron Bakker
29-02-2008, 07:33 AM
I would be manually labelling the transactions.

Heres some random thoughts
-I assume you download the bank statement from your bank, you should be able to download it as a .csv (comma delimited file) or .xls . Then you can load it into excel instead of copy and paste.
-Macros might also be helpful in excel. They are a way for automating common or repetitive tasks. You might want to check the excel help for more info on them.
-MS Money has a way of "learning" common transaction types and next month when you load a statement, it will identify and total the ones it already knows. This might be overkill.
Looks like I got some homework to do.