I formulated this real world example, which demonstrates how the use of excel functions can make work go extremely easy, and can boost confidence in handling bookkeeping errors and mistakes with speed and less time/effort invested.
This is a pool of recorded business transactions, categorized as business supplies. However, the economic substance of some of these transactions is that they came out of the business accounts for expenses relating to rental property (personal investment) and not for the incorporated business, hence GAAP or IFRS, says that this should be recorded as owners draw, and Tax law says that the non-business expenses qualifies as taxable deductions for personal income( property/real estate income). My client had rental property which is not a line of business under the current incorporated business.
Base on this, the transactions relating to the rental property are labelled as “CITY OF RED DEER”, occurring in 2024 and 2025. You will also notice that some transactions are not exactly the same to “CITY OF RED DEER” but slightly the same, example “CTY OF RED DEER” and “CITY OF RD DEER”. The rationale is that, any expense labelled as “CITY OF RED DEER” is not business expense, but rather expense incurred on a rental property, which affects personal tax and not business tax.
From this large pool of transactions (dates, amounts, description), it’s going to be impossible to manually sum up or look for all transactions recorded as CITY OF RED DEAR, as this will cost a lot of time, and you may get an inaccurate answer.
The Wild card character “*” can help you get an accurate answer with less time invested. SEE HOW:
With my two criteria; “ty of red deer” to represent any transactions in my chosen 1st criteria range (A3:A22) and “/2024” to represent any transactions in my chosen 2nd criteria range (B3:B22).
If I use a simple SUMIFS functions here, it will NOT give me an accurate total amount of expenses incurred for CITY OF RED DEER. Probably lesser amount. WHY? Because not all the transactions relating to CITY OF RED DEER are exactly spelled out the same.
SOLUTION:
To get an accurate answer, I can just join (ampersand/&) the wild card character “*”, with my first and second criteria (ty of red deer) and “/2024”, together with my criteria and sumifs ranges (A3:A22), (B3:B22) and (C3:C22). Joining(ampersand/&) the “*” in my SUMIFS function, means telling excel to sum the total value of transactions from my criteria ranges, that is identical to ty of red deer and that occurred in 2024 tax year. Gautham M. (FMVA)
NOTE:
- Criteria range (A3:A22) represents CITY OF RED DEER TRANSACTIONS THAT NEEDS TO BE SEPERATED, SINCE THEY ARE NOT BUSINESS EXPENSES
- Criteria range (B3:B22) represents the date the transactions occurred.
Do you thing excel is important for generating reports and spotting errors in bookkeeping?
