SUMIF question... I think

[theboydoug]

Hello Excel experts (Bows down appreciatively)

I am very new to excel and am having difficulties with what I think must be quite a simple problem.

I am setting up a ledger for my joint account. I have money going in and out for various reasons and want to be able to reference amounts to various columns and generate running totals.

I have attached my excel sheet.

Basically, when I type 38 in the reference column (referring to my house number) and the amount in equals 607.75 I want the food total to add 100 dollars to the running total in the food column.

I thought I could do it with SUMIF but it's not playing ball!

Any help is greatly appreciated.

[NBVC]

Put a 0 in G4 and then put this formula in G5, copied down....

=IF(C5=38,LOOKUP(9.99999E+307,$G$4:G4)+IF(F5>0,-F5,100),"")

I assumed you want to also include the subtractions when a value is entered in the "money out" column...

[theboydoug]

absolute genius... thank you very much.

This was clearly way beyond the simple solution I thought it was (for me anyway)

thanks again for your help

[theboydoug]

ok, that works a treat, but...

sometimes I am putting money into the account, under that reference (38) and it has nothing to do with food, it might be for bills etc. As far as I could see, with the formula you gave me when 38 if the reference it will always add 100 to the food total unless there is an amount in the money out column. Could you maybe refer the formula to the amount of 607.75???? Is that asking too much?

Also I would like to be able to copy the formula to the bills total section. I would probably have to rearrange/rename my columns to do that though.

Sorry if I haven't explained myself properly

[ratcat]

Forgot the zip.

Stop the submit half way through and didn't think it posted.

[ratcat]

ok, that works a treat, but...

sometimes I am putting money into the account, under that reference (38) and it has nothing to do with food, it might be for bills etc. As far as I could see, with the formula you gave me when 38 if the reference it will always add 100 to the food total unless there is an amount in the money out column. Could you maybe refer the formula to the amount of 607.75???? Is that asking too much?

Also I would like to be able to copy the formula to the bills total section. I would probably have to rearrange/rename my columns to do that though.

Sorry if I haven't explained myself properly

G'day Doug,

My intial though was my in/out spreadsheet that I half finished some 8 months ago. It was a point in my life where I was teaching myself excel.

Well I finished. *woot*

Then re reading your post it does and does not do what you would like to happen. You will have to re think on how you itemize your income and expenses to the references you want to uses.

Few notes on using the workbook.

First setup what code and debit or credit on the "DATABASE - SETUP" sheet.
All numbers and Description can be change and the information will follow on the other sheets. Becareful not to double up on the code numbers.

Recurring Payment

eg. Rent is $200, you place $200 in the "DEBIT" col and Wages you place $607.25 in the "CREDIT" col


Non recurring Payment

eg Entertainment: Expense is never the same you leave at open or you can put a message like "Enter $" in the debit col.


Well the next thing is play around with the spreadsheets. If you get struck please re post in the thread or send me a PM.

Remember its just an idea. HTH

Cheers

ratcat

Note: Original zip is 130kb. If you want the full original, PM me with your email, and more too happy to forward the final product

[theboydoug]

Thanks for your help ratcat, it's much appreciated.

I'll have a play around with the spreadsheet and see if it works for me.

Thanks again for your time

doug