Calculate ending balance based on un-checked items in another column

[jdhomeguy]

Hello, this is my first thread, so forgive me if I don't explain fully :-) I have a simple spreadsheet that I use for bank reconciliation, a check register if you will. Anyway, I have a column where I place an 'X' if the check has been cleared/reconciled or not. If the field is blank (not 'X') I would like to then take the numeric value of the check from another column and add that check value to calculate the ending balance. The formula I use right now to enter the ending balance is this ---> =IF(AND(ISBLANK(F3512),ISBLANK(G3512)),"",H3511-F3512+G3512).
Is there a way to calculate all this on the fly based on unchecked items and their values? I can provide an attachment if needed. Appreciate the help!

[dilipandey]

Hi jdhomeguy,

welcome to the forum.
Yes, please provide the attachment with your expected results. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[jdhomeguy]

Here's the attach. It's the 'Memo Balance' - Column I that I'm doing manually and would like to have it calculated.

[Ace_XL]

Maybe in I6

Please Login or Register  to view this content.

Copy down

[dilipandey]

okay.. don't u need to consider credit column ?
Also, I believe you need sum as per dates i.e., 1/31/13's entries should be considered for 1/31/13's total.. correct ?

Your example has not covered all the points, suggest you to come up with all possible situations so that you get complete solution else you'll keep adding change requests here in this thread

Regards,
DILIPandey
<click on below * if this helps>

[jdhomeguy]

You're right Dili. Would need to consider the credit column as well. So the value of the check/item is either Column F(Debit) OR Column G (Credit)

[dilipandey]

okay... and this will be for that day only.. and there could be multiple entry of a single date... correct?

Suggest you to think on all these possibilities and then come up with a concrete logic.. thanks.


Regards,
DILIPandey
<click on below * if this helps>

[jdhomeguy]

Correct. Pretty sure that's it as I can't see anything else besides including credits and multiple entries for a given day.

[dilipandey]

Okay.. considering "X", date, debit , credit, and balance columns, use below formula:-

=IF(E10="X","",SUMPRODUCT(($C$6:$C10=$C10)*($F$6:$F10))+SUMPRODUCT(($C$6:$C10=$C10)*($G$6:$G10))+$H10)

see attached:- Bank Register.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[jdhomeguy]

I downloaded your attach and removed 'X' from cell E7 which was a credit to test both debits and credits that were un-reconciled. With that change, I expected the memo balance in I10 to be 100.02 (42.00 in debits -10.73 in credits + 68.75 balance in H10). I've attached with that change - am I missing something?

[dilipandey]

E7 belongs to date 1/10/13 and,
I10 belongs to date 1/31/13

so both the date are different.. and thats what I already confirmed from you ... correct ?

Regards,
DILIPandey
<click on below * if this helps>

[jdhomeguy]

Guess I really didn't understand what you meant by including dates into this logic, sorry about that. But basically =H6+SUMIF($E$6:$E6,"<>x",F$6:F6) works if it could just account for credits (column G) as well. In short, this is much like a paper check register. Column H is the running account Balance taking into consideration ALL debits and credits to calculate your ending balance on a given day and that balance does not take into account whether items are actually reconciled at the bank or not (the X in column E). Column G (memo balance) is the same thing except it DOES take into account whether a debit or credit item is part of the same balance based on whether it is checked in column E or not.

[jdhomeguy]

Better to show by example, so here's an attached spreadsheet doing everything manually in the Memo Balance column. Any formula posted should be put in the column next to that so that it is clear by example that it comes out to the same running balances.

[jdhomeguy]

Guess I'll try another forum.....

[dilipandey]

See the attachment where I have given a formula which achieves results as you shown in "memo balance" column and I hope your have considered all the criteria this time while providing "memo balance"


Bank Register-updated.xlsx


Regards,
DILIPandey
<click on below * if this helps>

[kmcneill]

This formula you did works for me. I'm having trouble trying to get the =IF(ISERROR command to work with it so I don't get the #VALUE! returned to a blank cell.....Any idea? =H74+SUMIF($C$74:$C74,"<>X",F$74:F74)