| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 87575 | 2008-02-26 17:30:00 | excel question | Ron Bakker (356) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 643867 | 2008-02-26 17:30:00 | 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? |
Ron Bakker (356) | ||
| 643868 | 2008-02-26 17:45:00 | 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. |
the_bogan (9949) | ||
| 643869 | 2008-02-26 18:42:00 | 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. |
jberries (13063) | ||
| 643870 | 2008-02-26 21:02:00 | 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. |
rad_s4 (7401) | ||
| 643871 | 2008-02-27 00:13:00 | 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. |
the_bogan (9949) | ||
| 643872 | 2008-02-28 18:33:00 | 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. |
Ron Bakker (356) | ||
| 1 | |||||