The article How to calculate conditional subtotals in an Excel revenue sheet tracks a daily running balance in a simple revenue sheet. This type of subtotaling is common and can be accomplished with simple expressions—subtract this, add that and so on. That works fine until you want something more specific. In the first article, we use a simple IF() function to return a total of all transactions made on the same day; the results are the daily balance. In this article, we’ll work with expressions to return a monthly and yearly balance.
SEE: 69 Excel tips every user should master (TechRepublic)
I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. For your convenience, you can download the demonstration .xlsx and .xls files. The browser edition will support the functions and expressions used in this article. This article assumes you have basic Microsoft Excel skills.
How to get the transaction total and daily balance in Excel
We’ll start with the simple transaction sheet in Figure A. The first expression in column E,
E3: =C3-D3
subtracts debits from credits for the first transaction, returning a transaction total. The second expression, which is copied to the remaining records,
E4: =C5-D5+E4
adds the total from the previous transaction, creating a running total or balance.
The function in column F
=IF(B4=B3,””,E3)
returns the current transaction’s total if the date below doesn’t match the current transaction’s date. This function returns a total only when that condition isn’t met—it’s the FALSE action. That’s why there are empty cells in column F; those blanks represent days with multiple transactions. The result of this function repeats the running total in column E, but it’s easier to discern the daily subtotal when separated from the other daily totals. The sheet also contains a conditional format that highlights the daily subtotal.
Figure A
At this time, it’s important to note that the transactions (records) must be sorted in date order (column B). Most likely, given the nature of the data, the records are already in date order; but if not, you must sort by column B. Otherwise, the transaction and daily expressions won’t return the correct results. In addition, the orange font is a conditional format (in the downloadable demonstration file) that highlights the daily subtotal. We have our daily balance function, but let’s move on to another date subtotal—a monthly subtotal.
SEE: Windows 10: Lists of vocal commands for speech recognition and dictation (free PDF) (TechRepublic)
How to add a monthly balance in Excel
As you’ve seen, a daily balance requires only a simple IF() function to match dates before returning the balance for each day. Let’s suppose you also want a monthly balance. Doing so requires matching the month values for each transaction’s date. Fortunately, we can rely on the same logic already used to return the daily function
=IF(B4=B3,””,E3)
by adding the MONTH() function to the condition
=IF(MONTH(B4)=MONTH(B3),””,E3)
Enter the above function into cell G3 and copy it to the remaining sheet. (If you’re using a Table object, you won’t need to copy the function; the Table object will do that for you.) Figure B shows the results; you have only two monthly totals, April and May.
Figure B
The logic is the same as the daily subtotal, but the IF() function returns the transaction total from column E only when the months (column B) don’t match. For example, the function in G9 returns $1,248.96 because the month value in B9 doesn’t match the month value in B10. In other words, the transaction in the next record belongs to another month.
Now, let’s move on to a yearly balance.
How to get a yearly balance in Excel
Our simple data set has transactions for only one year, 2020, but that doesn’t matter. We can expect only one balance for the data set. The solution is the same, but this time, we’ll use the YEAR() function as follows:
=IF(YEAR(B4)=YEAR(B3),””,E3)
As you can see in Figure C, this function returns only one balance because all the dates are in 2020.
Figure C
Let’s see what happens if you add a 2021 date, as shown in Figure D. The function now returns two year balances. It also exposes an error—look closely at the monthly total column. Can you spot it?
Figure D
The monthly balance function only evaluates by months. Consequently, when the new transaction for May 2021 is added, it considers the month condition as met. They both are May dates. That’s why there isn’t a monthly balance for May 2020 in G12, which would be correct. I did this on purpose to show you what can happen. These simple functions might work for you but keep this possibility in mind. The solution is to add a conditional check for both the month and the year. That solution is much more complex.
How to use conditional formats for subtotals in Excel
The original article includes conditional formatting formulas that evaluate the transaction totals in column E so you can eliminate the additional conditional date columns, if you prefer. The demonstration file contains new rules for the monthly and yearly totals:
=MONTH($B4)<>MONTH($B3)
=YEAR($B4)<>YEAR($B3)
Note that the new rules take precedence in the following order: daily, monthly, yearly. That means if a transaction total meets more than one date condition, the larger date rule takes precedence. You might not want this, so you should be aware that you’ll need to revamp the formulas to suit your needs.
Stay tuned for more Excel tips
Because all the expressions, functions, and formulaic rules are simple, they won’t accommodate all situations, such as the monthly balance function. In a future article, I’ll show you a more complex function that handles both the month and the year for those data sets that require more control.