cd.thomson
|
| posted on 27/7/09 at 01:55 PM |
|
|
organising the car finances - excel experts
do any excel bods know how I could sum the incoming credit card balance with the outgoing credit card payment and have it display in the bottom credit
card field?
The payment is manual and the date varies so could come anywhere in the list. The reason comment will be standardised though.
I've always struggling with getting sumif and if functions to work. Is it complex enough to require some code?
http://spreadsheets.google.com/ccc?key=0Aib5lhElREHsdDhRWkMtOEgwbkdDUmV6Q29xNjhCeWc&hl=en
ETA: you need a google account to view it, if anyone knows a better way to upload let me know
ETA2: couldnt have got this post more wrong, 6 edits later I think it makes sense..
[Edited on 27/7/09 by cd.thomson]
Craig
|
|
|
|
|
Mr Whippy
|
| posted on 27/7/09 at 02:47 PM |
|
|
the topics in excels own help function are very good when it comes to the IF formulas
simply take one of their examples and modify it
Fame is when your old car is plastered all over the internet
|
|
|
mookaloid
|
| posted on 27/7/09 at 02:49 PM |
|
|
It won't let me look at the spreadsheet as I apparently have to request access - which I have now done
"That thing you're thinking - it wont be that."
|
|
|
cd.thomson
|
| posted on 27/7/09 at 03:07 PM |
|
|
http://spreadsheets.google.com/pub?key=t8QZC-8H0nGCRezCoq68Byg&single=true&gid=0&output=html
this might work better, i set the permissions to public 
Craig
|
|
|
fov
|
| posted on 27/7/09 at 05:49 PM |
|
|
2 ways I can see are:
1 - The quick and dirty one.
On another workbook have a vlookup to find all values which are for the credit card and sum them at the bottom. Then reference that vaule in the first
workbook.
2 - The cleaver way.
Write a function to do it.
|
|
|
Paradoxia0
|
| posted on 27/7/09 at 05:55 PM |
|
|
OK, so if I am understanding you...
You want the total in next to credit card to be the sum of everything in incoming minus whatever Outgoing "CREDIT CARD" payments are
listed but missing all other outgoings?
If so, if you assume that Credit Card is in cell A33 then this formula in C33 should do the trick:
=SUM(B7:B29)-SUMIF(D7: D29,"CREDIT CARD",C7:C29)
If I have got what you are asking wrong then if you try to clarify what you are after I will try again for you.
Mark
(NOTE: no space between the (D27: and D29... I put that in else you get (D27 29.... )
[Edited on 27/7/09 by Paradoxia0]
There is no replacement for displacement...
|
|
|
cd.thomson
|
| posted on 27/7/09 at 06:15 PM |
|
|
Slightly simpler than that: i want the value next to CREDIT CARD to be subtracted from the credit card balance at the very top of the page in the
bottom credit card balance to give the final balance on the card after I've made a payment against it
So if the very top credit card was -£50 (i.e. in debt) and the outgoing CREDIT CARD value is -£25 (- indicating money leaving my account) then I would
like the very bottom credit card cell to show -£25
thanks for your replies everyone. VLOOKUP could be an easy alternative.
Craig
|
|
|
fov
|
| posted on 27/7/09 at 06:23 PM |
|
|
Thats dead easy.
=A3-SUMIF(E4:E11,"cc",D4: D11)
(change the range to fit your sheet)
You could do it with VLOOKUP but this will account if you make two payments to the credit card.
[edit to add a space so you dont get a ]
[Edited on 27/7/09 by fov]
|
|
|
cd.thomson
|
| posted on 27/7/09 at 06:26 PM |
|
|
thanks everyone
Craig
|
|
|
