How to subtotal transactions by conditional date components in Excel

If you need a monthly or annual balance for revenue transactions in Microsoft Excel, you’re in luck because both are easy! Here’s how to find the totals you need.

table and calculator

Photo: utah778, Getty Images/iStockphoto

The article tracks how to calculate conditional subtotals in an Excel revenue sheet Daily operating balance in a simple revenue statement. This kind of subassembly is common and can be achieved with simple expressions – subtract this, add that, etc. This works fine until you want something more specific. In the first article, we use a simple IF() function to return the 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.

We see: 69 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on Windows 10 64-bit, but you can use earlier versions. For your convenience, you can download demo files .xlsx and . xls. The browser version will support the functions and expressions used in this article. This article assumes that you have basic Microsoft Excel skills.

How to get total transactions and daily balance in Excel

We will start with the simple transaction sheet in Figure A. The first expression in column E,

E3: = C3-D3

Subtracts the debit from the credits for the first transaction, and returns the 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 current total or balance.

The function in column F

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

Returns the total of the current transaction if the date below does not match the date of the current transaction. This function returns the total only when this condition is not met – it is a FALSE action. That’s why there are empty cells in column F; These blanks represent days with multiple transactions. The result of this function repeats the running total in column E, but the daily subtotal is easy to distinguish when separated from other daily totals. The sheet also has conditional formatting that highlights the daily subtotal.

Figure A

excelconditionaldates-a.jpg

This simple sheet shows transaction totals and daily subtotals.

At this time, it is important to note that the transactions (records) must be sorted in date order (column B). Most likely, due to the nature of the data, the records are already in chronological order; But if not, you must sort by column B. Otherwise, transaction and daily expressions will not return correct results. Additionally, the orange font is a conditional formatting (in the downloadable demo file) that highlights the daily subtotal. We have the daily balance function, but let’s move on to another date subtotal – a monthly subtotal.

We see: Windows 10: Voice Command Menus for Speech Recognition and Dictation (Free PDF) (TechRepublic)

How to add a monthly balance in Excel

As you’ve seen, the daily balance requires a simple IF() function just to match the dates before returning the balance for each day. Let’s say you also want a monthly credit. Doing so requires matching month values ​​to the date of each transaction. 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 case

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

Enter the above function in cell G3 and copy it to the remaining sheet. (If you’re using a table object, you don’t need to copy the function; the table object will do it for you.) Figure B Your results only show two monthly totals, April and May.

Figure B

excelconditionaldates-b.jpg

Add the MONTH() functions to return the monthly balances.

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 value of the month in B9 does not match the value of the month in B10. In other words, the transaction in the next record belongs to another month.

Now, let’s move on to the annual balance.

How to get an annual balance in Excel

Our simple data set contains only transactions for 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 will use the YEAR() function as follows:

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

as you see in Figure C, this function returns only one balance because all dates are in 2020.

Figure C

excelconditionaldates-c.jpg

The YEAR() function returns only one yearly balance.

Let’s see what happens if you add the date of 2021, as shown in figure d. The function now returns the balances of two years. It also reveals an error – look closely at the monthly total column. Can you discover it?

figure d

excelconditionaldates-d.jpg

Add a date for 2021 to return two annual credits.

The monthly balance function is only evaluated by months. Thus, when adding the new transaction for May 2021, it considers the month condition as fulfilled. Both are May dates. That’s why there is no monthly balance for May 2020 in G12, which would be true. I did it on purpose to show you what could happen. These simple functions may work for you but keep this possibility in mind. The solution is to add a conditional check for both the month and the year. This solution is more complicated.

How to Use Conditional Formats for Subtotals in Excel

The original article includes conditional formatting formulas that evaluate transaction totals in column E so you can remove additional conditional date columns, if you prefer. The demo file contains new rules for 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. This means that if the total transaction satisfies more than one date condition, the larger date rule will have priority. You may not like it, so be aware that you will need to revamp the formulas to suit your needs.

Stay tuned for more Excel tips

Since all expressions, functions, and formula rules are simple, they will not accommodate all situations, such as the monthly balance function. In a future article, I’ll show you a more complex function that deals with both month and year for datasets that require more control.

see also