Deposit Summary

Shlomo Stein

Deposit Summary-Test (2).pdf

The attached sheet is a sample of a project for tying out cash at the end of the month.

The left side (Deposit amount) is the amount that was posted in our billing software to the ledger. The right side (Bank Credits) is the amount that hit the bank. We need to match our ledger to the bank statements and tie them out.

Column6 and Column4 have the Countif formula to count if the bank credits are in the deposit amount (column6) and count if the deposit amount is in the bank credits (column4). The red rows are conditional formating for the missing items (0).

The green rows are for duplicate items (2). For example, in row 25, 55.85 is twice in the bank credits. However, I would really like to have a solution that, for example, the 55.85 in row 25 since it's duplicated twice on both sides (deposit amount and bank credit); it shouldn't highlight that row. Line 54 should be highlighted since it's only once on the deposit amount and twice on the bank account side, but line 25 is twice on both sides, so it shouldn't be highlighted.

Please let me know if you have a solution

(My coworker @Judy Tropper asked a similar question).

bcwilson.ca

Try this..

I Did a logic check and used a COUNTIF and a COUNTIFS

1. First I look for Deposit Amount Match.. like you do
   1. If it is Zero it needs to be Red as there is an issue so return 0
   2. If it is 1 it is good and it passes a 1
2. Now if it is 2 we go a bit further.
   1. If there is the same amount on deposited the same date then we know that particular record is good so pass a 1
   2. If there is not the same amount on the same date then it needs to be checked so pass a 2

You can see this with the $238.79 deposited on the 20 of January that the bank put in on the 22nd. I would assume you would want to check that..

So for Column4

=IF(COUNTIF([Bank Credits]:[Bank Credits], [Deposit Amount]@row) = 1, 1, IF(COUNTIF([Bank Credits]:[Bank Credits], [Deposit Amount]@row) = 0, 0, IF(COUNTIFS([Bank Credits]:[Bank Credits], [Deposit Amount]@row, [Bank Deposit Date]:[Bank Deposit Date], [Posting Date]@row) = 0, 2, 1)))

Now the other way for Column6

=IF(COUNTIF([Deposit Amount]:[Deposit Amount], [Bank Credits]@row) = 1, 1, IF(COUNTIF([Deposit Amount]:[Deposit Amount], [Bank Credits]@row) = 0, 0, IF(COUNTIFS([Deposit Amount]:[Deposit Amount], [Bank Credits]@row, [Posting Date]:[Posting Date], [Bank Deposit Date]@row) = 0, 2, 1)))

Deposit Test.pdf

You might want to create a new column and test until you are sure that I got it

But it is highlighting the $238.79 deposited on the different date in both the Deposit line and the Bank Credit Line

Let me know if that helps

Shlomo Stein

https://community.smartsheet.com/discussion/comment/279182#Comment_279182

@bcwilson.ca Thank you so much for taking the time to respond, I appreciate it.

Your logic looks very good, however, I have an issue with row 54, it should be a 2, since it was deposited twice in bank credits (rows 28 and 51) but only posted once in deposit summary (row 54). That's (the 25000) actually the only item on this list that needs a warning.

Deposit Summary vs Bank Account Worksheet (1).pdf

Please let me know if you have a solution. Thank you