Bank reconciliation using Microsoft Excel IF function and COUNTIF function formulas

Bank reconciliation using Microsoft Excel IF function and COUNTIF function formulas

How to perform bank reconciliation using Microsoft excel IF function and COUNTIF function formulas in order to improve efficiency is the theme of this post.

I have written an earlier piece on the fundamentals of bank reconciliation statement. However, a lot of forensic accountants that have next to zero margin error have been spending hours doing bank reconciliations manually – this is complete waste of valuable time.

It is no longer enough for you to know how to perform your bank reconciliation manually or relying on 3^rd party tool for this critical function of an accountant. You need to learn how to build financial models using Microsoft excel and that is what you are going to learn in the next few paragraphs of this article on how to perform bank reconciliation using Microsoft Excel IF Function and COUNTIF Function.

You can conclude with your bank reconciliation task in as short as (1) one hour simply by applying the simple excel formula that you are about to be exposed to in this article on How to perform bank reconciliation using Microsoft excel IF function and COUNTIF function formulas. – sit back and enjoy this ride that is poised to take you to the land super efficiency while doing bank reconciliations. Download the file below to follow along.

How to prepare bank reconciliation using Microsoft Excel IF function and COUNTIF function formulas

The first thing to do is to clean up your data following the simple three steps below

1. Download the bank statement(s) from your online banking platform and Cashbook (from your accounting software) in excel format.
2. Alternate the sides vertically on two sheets (i.e take the entries on the left side of the bank statement, place them below the items on the right side of the cashbook on one sheet and then take the entries on the right side of the bank statement, place them below the items on the left side of the cashbook on another sheet) – you now have two worksheets.
3. Get rid of spaces. This is very important in order to avoid unnecessary errors and issues

The data is now ready for you to take the next two simple actions

1. Count unique figures: The idea here is to count all identical figures so that the matching phase can be done seamlessly. However, counting these figures is non trivial task for we humans so excel comes to the rescue.  Use the IF Function formula below to count and display the frequency of unique figures in the dataset.

=IF(B2<0,-B2&”-“&COUNTIF(B$2:B2,B2),B2&”-“&COUNTIF(B$2:B2,B2))

Please note that B2 in the formula represents the ‘Amount Column’ while this formula is to be entered into the ‘Unique’ column as shown in the image below. Notice that 30,000 for example occurred (5) five times each instance numbered accordingly.

See image below

EntryDate	Amount	Unique
22-Sep-21	-75,000.00	75000-1
29-Sep-21	-30,000.00	30000-1
29-Sep-21	-30,000.00	30000-2
29-Sep-21	-30,000.00	30000-3
29-Sep-21	-30,000.00	30000-4
29-Sep-21	-30,000.00	30000-5
29-Sep-21	-60,000.00	60000-1
29-Sep-21	-37,500.00	37500-1
29-Sep-21	-37,500.00	37500-2
29-Sep-21	-112,500.00	112500-1
29-Sep-21	-112,500.00	112500-2
29-Sep-21	-112,500.00	112500-3
29-Sep-21	-112,500.00	112500-4
29-Sep-21	-112,500.00	112500-5
29-Sep-21	-112,500.00	112500-6
29-Sep-21	-112,500.00	112500-7

• Match the uniquely identified figures: The last step in using IF Function and COUNTIF function of excel to perform bank reconciliation is to now match the uniquely identified and counted entries above. Again doing this manually will be a herculean task and excel once again came to the rescue.

Simply enter the formula below into next column – I call ‘Unique’ in this example but you can call it anything.

=IF(COUNTIF($C$2:$C$113,C2)=2,”x”,””)

What this does is to do the leg work of matching each entries in pairs and mark them with the letter ‘X’. Any item that is not matched after this step are what we call ‘reconciling items’ that needs to be manually reconciled. The entries that are not marked with the letter ‘X’ are entries that only appear on one side of the equation and they are mostly all forms of bank charges. Perform same on the other sheets to to complete the reconciliation process.

We have successfully done our bank reconciliation using excel. This whole process doesn’t take more than 45mins to conclude by the time one masters these simple steps of performing bank reconciliation using excel formula.

See image of complete bank reconciliation below

EntryDate	Amount	Unique	Matched
22-Sep-21	-75,000.00	75000-1	x
29-Sep-21	-30,000.00	30000-1	x
29-Sep-21	-30,000.00	30000-2	x
29-Sep-21	-30,000.00	30000-3	x
29-Sep-21	-30,000.00	30000-4	x
29-Sep-21	-30,000.00	30000-5	x
29-Sep-21	-60,000.00	60000-1	x
29-Sep-21	-37,500.00	37500-1	x
29-Sep-21	-37,500.00	37500-2	x
29-Sep-21	-112,500.00	112500-1	x
29-Sep-21	-112,500.00	112500-2	x
29-Sep-21	-112,500.00	112500-3	x
29-Sep-21	-112,500.00	112500-4	x
29-Sep-21	-112,500.00	112500-5	x
29-Sep-21	-112,500.00	112500-6	x
29-Sep-21	-112,500.00	112500-7	x
29-Sep-21	-30,000.00	30000-6	x
29-Sep-21	-30,000.00	30000-7	x
29-Sep-21	-1,658,660.72	1658660.72-1	x
29-Sep-21	-30,000.00	30000-8	x
29-Sep-21	-112,500.00	112500-8	x
29-Sep-21	-112,500.00	112500-9	x
29-Sep-21	-685,948.91	685948.91-1	x
29-Sep-21	-1,187,236.36	1187236.36-1	x
30-Sep-21	-51,857.85	51857.85-1	x
30-Sep-21	-207,424.68	207424.68-1	x
30-Sep-21	-274,462.36	274462.36-1	x
30-Sep-21	-432,896.37	432896.37-1	x
24/09/2021	75000	75000-1	x
29/09/2021	685948.91	685948.91-1	x
29/09/2021	1187236.36	1187236.36-1	x
29/09/2021	112500	112500-1	x
29/09/2021	112500	112500-2	x
29/09/2021	112500	112500-3	x
29/09/2021	112500	112500-4	x
29/09/2021	112500	112500-5	x
29/09/2021	112500	112500-6	x
29/09/2021	112500	112500-7	x
29/09/2021	112500	112500-8	x
29/09/2021	112500	112500-9	x
29/09/2021	60000	60000-1	x
29/09/2021	30000	30000-1	x
29/09/2021	30000	30000-2	x
29/09/2021	30000	30000-3	x
29/09/2021	30000	30000-4	x
29/09/2021	30000	30000-5	x
29/09/2021	30000	30000-6	x
29/09/2021	30000	30000-7	x
29/09/2021	30000	30000-8	x
29/09/2021	37500	37500-1	x
29/09/2021	37500	37500-2	x
29/09/2021	1658660.72	1658660.72-1	x
29/09/2021	51857.85	51857.85-1	x
29/09/2021	207424.68	207424.68-1	x
29/09/2021	274462.36	274462.36-1	x
29/09/2021	432896.37	432896.37-1	x
01-Sep-21	-80	80-1
01-Sep-21	-6	6-1
01-Sep-21	0	0-1
06-Sep-21	-50	50-1
10-Sep-21	0	0-2
10-Sep-21	0	0-3
10-Sep-21	0	0-4
10-Sep-21	0	0-5
10-Sep-21	0	0-6
10-Sep-21	0	0-7
10-Sep-21	0	0-8
20-Sep-21	-50	50-2
20-Sep-21	-50	50-3
20-Sep-21	-50	50-4
20-Sep-21	-50	50-5
20-Sep-21	-50	50-6
20-Sep-21	-50	50-7
20-Sep-21	-50	50-8
22-Sep-21	-50	50-9
22-Sep-21	-3.75	3.75-1
29-Sep-21	-124	124-1
29-Sep-21	-25	25-1
29-Sep-21	-1.88	1.88-1
29-Sep-21	-25	25-2
29-Sep-21	-1.88	1.88-2
29-Sep-21	-25	25-3
29-Sep-21	-1.88	1.88-3
29-Sep-21	-25	25-4
29-Sep-21	-1.88	1.88-4
29-Sep-21	-25	25-5
29-Sep-21	-1.88	1.88-5
29-Sep-21	-50	50-10
29-Sep-21	-3.75	3.75-2
29-Sep-21	-25	25-6
29-Sep-21	-1.88	1.88-6
29-Sep-21	-25	25-7
29-Sep-21	-1.88	1.88-7
29-Sep-21	-50	50-11
29-Sep-21	-3.75	3.75-3
29-Sep-21	-50	50-12
29-Sep-21	-3.75	3.75-4
29-Sep-21	-50	50-13
29-Sep-21	-3.75	3.75-5
29-Sep-21	-50	50-14
29-Sep-21	-3.75	3.75-6
29-Sep-21	-50	50-15
29-Sep-21	-3.75	3.75-7
29-Sep-21	-50	50-16
29-Sep-21	-3.75	3.75-8
29-Sep-21	-50	50-17
29-Sep-21	-3.75	3.75-9
29-Sep-21	-25	25-8
29-Sep-21	-1.88	1.88-8

Please kindly share this with your contact if you find the content useful.