digestVidSign in
summary

excelisfun - Excel Magic Trick 1410: Calculate Closing Balance for Each Month from Balance Transaction Table

Thumbnail of the video “excelisfun - Excel Magic Trick 1410: Calculate Closing Balance for Each Month from Balance Transaction Table”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Technology Science
Tags: technology, science
Views: 2 · Last viewed: 2026-10-16 00:54 UTC

Short Summary

Main recommendation: To get each month's closing balance from a transaction table, find the last transaction date on or before each month-end, then feed that date into SUMIFS as its criteria. The video shows several ways to find that date, with MAXIFS (Excel 2016 Insider Edition or later) as the primary method, plus AGGREGATE, VLOOKUP, and an IF/MAX array approach for older versions.

Key Points

  • The problem: a month's ending balance does not always fall on the month-end date, so you cannot simply use the month-end as the SUMIFS criteria.
  • Method one (MAXIFS): use MAXIFS to return the latest date on or before the month-end, then use that date as SUMIFS criteria.
  • Method two (AGGREGATE, Excel 2010 or earlier): AGGREGATE function 14 (LARGE) with option 6 (ignore errors) and an array of dates divided by a TRUE/FALSE test returns the largest qualifying date.
  • Method three (VLOOKUP, only if the date column is sorted): approximate-match lookup on the date column returns the last date on or before the month-end.
  • Method four (IF and MAX, Excel 2007 or 2003): MAX over an IF-filtered date array, entered with Ctrl+Shift+Enter, returns the same date.

Details Worth Noting

  • Concrete instructions:
    • Set up the table with month-end dates as row headers (for example, 1/31/2017, 2/28/2017).
    • MAXIFS version: enter =SUMIFS(TableName[Balance AR], TableName[Date], MAXIFS(TableName[Date], TableName[Date], "<="&[month-end cell])). Press Ctrl+Enter and fill down.
    • AGGREGATE version: enter =SUMIFS(TableName[Balance AR], TableName[Date], AGGREGATE(14, 6, TableName[Date]/(TableName[Date]<=[month-end cell]), 1)). Press Ctrl+Enter and fill down.
    • VLOOKUP version (sorted dates only): enter =SUMIFS(TableName[Balance AR], TableName[Date], VLOOKUP([month-end cell], TableName[Date], 1)) with approximate match (no range lookup argument), format the result as a date (Ctrl+1), and fill down.
    • IF/MAX version (Excel 2007 or 2003): enter =SUMIFS(sum range, criteria range, MAX(IF(date column<=month-end, date column))) and confirm with Ctrl+Shift+Enter, then verify the curly braces appear in the formula bar.
    • Verify the result: after sorting the table with largest balances at the top, MAXIFS, AGGREGATE, and the IF/MAX array still work, but the VLOOKUP version fails.
  • The video notes that MAXIFS and AGGREGATE do not depend on sort order, which is the main reason to prefer them over VLOOKUP.
  • Using a table reference (clicking the header's downward arrow to insert the structured name) keeps formulas readable.
  • The AGGREGATE trick works because dividing by FALSE (zero) produces errors, which option 6 ignores.

Bottom Line

For reliable month-end balances, use MAXIFS (or AGGREGATE on older Excel) to find the last qualifying date and pass it to SUMIFS, since these methods work regardless of how the data is sorted.