Need help creating a formula to add new $ amounts to running totals

[sjcap]

I use Excel 2011 for Mac on a regular basis to create relatively simple spreadsheets for my business. One spreadsheet I created is to track my deductible expenses throughout the year. I have an "amount" column that is a running total for each type of expense and that I manually add to as I incur new deductible expenses.

I really don't know what to call the type of formula I need but is there a way to set up a formula in the "amount" column cells that would allow me to simply enter a new expense dollar amount, possibly in another cell in the worksheet, and it would add that amount to the running totals for each type of expense? I've tried a few different formulas but keep getting the circular reference error message.

Thank you for your assistance.

[Pete_UK]

It is probably better to put the total deductible (and the income figure) at the top of your sheet, and to freeze the panes so the totals are always visible even if you scroll down your sheet. Then you can just keep adding new expenses to the bottom of what you have already entered, and you can use a formula like this to give you the totals:

=SUM(B$3:B5000)

You can make the second cell reference even larger if necessary.

Hope this helps.

Pete

[etaf]

you could use a SumiF( )
to add up the total by each type of expense

if you listed all the different type of expenses in a column and then in the next column you could have a running total of that type of expense

so

Parking SUMIF( Range with the list of type, new column with list, range with the amounts)

if you could post a sample spreadsheet - rather than a image

[etaf]

you could use a SumiF( )
to add up the total by each type of expense

if you listed all the different type of expenses in a column and then in the next column you could have a running total of that type of expense

so

Parking SUMIF( Range with the list of type, new column with list, range with the amounts)

if you could post a sample spreadsheet - rather than a image

[sjcap]

Thank you for the replies although I'm not sure I understand how to implement your suggestions in my spreadsheet. To reiterate, what Iwould like to achieve is to be able to enter a new expense dollar amount somewhere in the spreadsheet and have that new amount added to the running total figure for the particular type of expense. As requested I've attached the .xlsx file.

Thank you,
Steve

[etaf]

I have created a sheet called
dataentry
here you can just enter the expense and type of expense
and then on sheet
BUOF 2014

I have summed up all the different expenses into the descriptions you have

Hope that is something like you wanted
other wise a detailed description would be needed and some examples in the spreadsheet

[sjcap]

etaf, thank you for the example. I appreciate you doing that. Could you elaborate on the logic of the formula you created so I can understand how you created it? I don't like to just use information I've received from others without learning how to do it myself.


The formula and data sheet example you provided does do what I was wanting in that it allows entry of expenses on the data sheets and updates the YTD totals on the BUOF14 (business use of home) sheet, however, in order to enter multiple new expenses for a specific category of expense, I have to have multiple identical "lists of expenses" like you've provided in the data entry sheet you created (I think you had three in there). This could work if I created a data entry sheet for every month with each months sheet having say 6 or 7 of the "lists of expenses" for data entry and then have the formula in the BUOF14 sheet track all the data entry sheets. Is this possible to do with your formula?

I don't want to overcomplicate this as it is a pretty simple form and I don't have a lot of expenses every month but my curiosity and wanting to learn more advanced usage of Excel made me think there might be a way to not have to pick up a calculator to tally up receipts and then manually add them to the YTD total on the BUOF14 sheet.

Thanks again,

[etaf]

the formula uses
=SUMIF(DataEntry!A:A,'BUOF 2014'!A3,DataEntry!B:B)

http://office.microsoft.com/en-gb/ex...005209292.aspx

so if in the BUOF14 - you have all the descriptions you are likely to use
then in the data entry you can enter any of those descriptions and the amount

then the formula SUMIF
will look down the data sheet descriptions and match with the description on the BUOF14 sheet and sum all the values that match

i put in the data sheet all the descriptions you had in BUOF14

so going back to the months
yes we could add in the dates
actually in would be so much easier if we used a pivot table

all you need to do is to add in the data sheet the expensive name, the value and the date
then refresh the pivot table and all when be done automatically

have a look at the attached and see what you think - if interested we can through it all

[sjcap]

Thanks again for your help. I'll play around with your ideas and get back with you. Appreciate your time.

[etaf]

your welcome, post back if you need any clarification required

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here