# Month-end cash balance from the running cash balances

1. ## Month-end cash balance from the running cash balances

Hello,
Attached is a sample of an income and expenses report (1st tab), and month-end cash balance report (2nd tab), that I maintain.
Right now, I am using “=Report!E12” to record the cash balance for the month of January 2012. This manual method is tedious and potentially can create an error if not careful.

I have been researching the internet for an excel formula of a cash balance for a specific month and year.

Sincerely
Dache416

2. ## Re: Month-end cash balance from the running cash balances

Here, try this:
Formula:
`Please Login or Register  to view this content.`

Edit: You can also add dates instead of number in A column of 2nd sheet and format as MMMM to show months, but I had to change to numbers for the test.

3. ## Re: Month-end cash balance from the running cash balances

A word of caution with zbor's solution above: it relies on "Interest paid" being the last transaction each month. If that's true then it will give the closing balance of each month of the year before the year shown in column 1 of the Month-end Report tab. I suspect you need to add a new column for 2012 in which case you would need to remove the "-1" from the "'Month-end Report'!B\$1-1" part of the formula.

4. ## Re: Month-end cash balance from the running cash balances

Hello Zbor,

The formula worked as it shown on the edited attachment. However, the formula would not work, if I combine the two reports together (meaning both tables is in one worksheet) using your formula or the formula below:

=SUMPRODUCT((MONTH(\$A\$3:\$A\$25019)=1)*(YEAR(\$A\$3:\$A\$25019)=2012)*(\$B\$3:\$B\$25019="Interest Paid"),\$G\$3:\$G\$25019)

Dache416

5. ## Re: Month-end cash balance from the running cash balances

Attached is the modify report spreadsheet

6. ## Re: Month-end cash balance from the running cash balances

You looking for 2013 while all data is in 2012.
You need to take -1 from 2013 to get correct result.

Formula:
`Please Login or Register  to view this content.`

Also, you looking for month 2 in January and month 1 in February.

Maybe you should change into
Formula:
`Please Login or Register  to view this content.`

7. ## Re: Month-end cash balance from the running cash balances

Thank you for your help Zbor

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1