How to calculate conditional subtotals in an Excel revenue sheet

Subtotals are common in a lot of Excel spreadsheets, and they’re usually the result of a simple expression or function. For example, you might have a simple revenue sheet that tracks debits and credits, and returns the current balance. This is easily accomplished with a simple credit–debit expression. However, there’s no subtotal to speak of. Now, let’s throw in a monkey wrench—suppose the boss wants to see a daily balance. That’s going to take a bit more work, but it’s easily accomplished by adding a condition to the mix—the condition being the date.

Disclosure: TechRepublic may earn a commission from some of the products featured on this page. TechRepublic and the author were not compensated for this independent review.

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 file. The browser edition will support the functions and expressions used in this article. This article assumes you have basic Microsoft Excel skills.

The basic debit-credit spreadsheet

Let’s review the simple revenue accounting sheet shown in Figure A. There’s a total for each transaction (record) and the last transaction shows the current total. At this point, the sheet has only two simple expressions:

E3: =C3-D3

E4: =C3-D3+E3

The remaining cells, E5:E12 contain a relational version of the expression in E4, which adds the transaction debit or credit to the previous total to return a running total. It’s a simple, but effective data set in a Table object. However, there’s no daily balance (or subtotal).

Now, let’s suppose someone wants to see the daily balance—is that a subtotal or a running total? It’s kind of both, but regardless of what you call it, you’ll need something more robust that the two simple expressions you’ve seen thus far.

Figure A

It’s worth noting that everything depends on the dates being sorted correctly. In this case, it makes sense that the input order is already sorted, but depending on how you use this solution, that might not be the case. If your dates aren’t sorted, be sure to do that first. Otherwise, you will not get a true picture of your transactions.

SEE: How to easily print Excel sheets in black and white (TechRepublic)

How to add daily calculations

Right now, the sheet is extremely simple, but it will easily accommodate the request for a daily balance. We’ll add a new column to the Table, column F, for the daily subtotal. To do so, enter the header text, Daily Subtotal, into cell F2, and the Table will extend automatically. In cell F3, enter the following function and then copy it to the remaining records:

=IF(B4=B3,””,E3)

The IF() function evaluates a simple condition: If the date in the next record is the same as the date in the current record, return an empty string; otherwise, copy the transaction total for the current record. In other words, if the two dates are the same, we haven’t reached the end of the daily transactions. As you can see in Figure B, that daily balance already exists in the transaction total, but the IF() function displays only the last transaction total for each day.

Figure B

This is where our terms become a bit cloudy. As you can see, the IF() function returns a daily balance, or subtotal. Before continuing, you might want to change the header text in F2 to Daily Balance because in this case, balance is more accurate that subtotal. The terms you use will be dictated by your data and how you’re using it.

SEE: How to split a column using an IF() function in Excel (TechRepublic)

You might think it a bit inefficient to duplicate values and assign different terms. In column form, the daily balance is easier to spot when separated from the other transactions; you know that each value represents a full day’s worth of transactions. But you don’t really need a second column to do so; you could highlight the value in the transaction total column instead.

How to use a conditional format

If you have a busy sheet with lots of columns, you might not want to add a new column to discern the daily balance more easily. When that’s the case, use a conditional format as follows:

1. Select the data you want to highlight. To highlight the entire row (excluding column F), select B3:E12.
2. On the Home tab, click Conditional Formatting and choose New Rule from the dropdown.
3. In the resulting dialog, choose the last item in the top pane (Use a Formula…).
4. Enter the following formula in the lower pane:
   =$B4<>$B3
5. Click Format.
6. Click the Font tab and choose bright orange from the Color dropdown.
7. Click OK to see the formula and the format (Figure C).
8. Click OK to return to the sheet, shown in Figure D.

Figure C

Figure D

This simple expression changes the font color for the selected range B3:E12 when the consecutive dates in column B don’t match, indicating that the record is the last record for the current date. (The orange font looks different from white to blue fill colors; it isn’t two different font colors.)

“Subtotal” or “daily balance” doesn’t matter

Regardless of your terminology—a daily subtotal or balance—using the transaction date as a condition for calculating it is the key. If it doesn’t work correctly, make sure your records are sorted by date. In a subsequent article, we’ll work with conditional running subtotals by date components: date, month, and year.