FILTER Function for GL Audit and AR Aging: Multi-Condition Boolean Logic for Accountants

Why Pivot Tables Aren't Enough for AR Aging

Pivot tables are aggregation tools. They're excellent at summing a static range into buckets, but they have three real limitations for the way accountants actually use ageing data day to day.

FILTER returns a live, spillable array based on however many conditions you combine, and because it's a formula, it recalculates the instant source data changes — no refresh button required. If you're reconciling balances between entities rather than customer receivables, the two-way matching approach in our XLOOKUP for intercompany reconciliation guide is the more direct fit.

FILTER Basics: The Boolean Logic Accountants Actually Need

The general FILTER function syntax is straightforward:

The part that actually matters for accounting use cases is what goes into include. This is where you combine multiple conditions using Excel's array-multiplication trick for AND, and array-addition for OR — and it's the part most generic tutorials skip entirely, because they're built around single-condition examples.

AND Logic with * — Filtering a GL for Unposted or Unusual Entries

To return only GL lines that meet every condition simultaneously, multiply the condition arrays together:

Each condition evaluates to an array of TRUE/FALSE, which Excel treats as 1/0 when multiplied. A row only survives if every condition returns 1 — this is genuine AND logic, and it scales to as many conditions as you need by adding more multiplied terms.

OR Logic with + — Catching Multiple Exception Conditions at Once

For a GL exception report where a line should be flagged if it meets any of several conditions, add the condition arrays instead of multiplying:

Any row with at least one TRUE condition returns a value greater than zero, which FILTER treats as included. This single line replaces what would otherwise be three separate manual filters or a nested IF formula three levels deep.

The Scenario: Building a Dynamic 30/60/90 Aging Report

To make this concrete, we'll build an ageing report for a fictional B2B receivables ledger — Meridian Group's open invoice population, spanning multiple customers across several account types. The ledger has one row per invoice: customer name, invoice date, due date, original amount, and amount received to date.

The goal is a report that automatically recalculates its ageing buckets and total exposure every time a payment is logged against an invoice, with no manual refresh step.

Step 1: FILTER the Open Invoice Population

Start by isolating only invoices that still carry a balance — this is the population everything else builds on:

This single condition does more work than it looks like. A fully paid invoice drops out of the array automatically the moment a payment brings the received amount up to the original amount — no separate "paid off" flag to maintain, no manual archiving step.

Step 2: Age Bucket Classification Inside the Array

With the open population isolated, calculate days outstanding and classify each invoice into a bucket using nested IFS logic inside a calculated column:

Because this recalculates against TODAY(), the bucket an invoice sits in shifts automatically day by day — an invoice doesn't need anyone to manually move it from "Current" to "31-60 Days" once it crosses the threshold.

Step 3: Layering an ECL Allowance Calculation

For IFRS 9 expected credit loss provisioning, extend the bucket classification with a provisioning rate per bucket, then multiply through the open population from Step 1, feeding into a summary table that applies your agreed provision rate matrix:

The provision rates themselves are a judgement call agreed with your audit team, not something FILTER determines — but once the bucket classification is dynamic, the ECL calculation that sits on top of it stays current automatically rather than needing manual recalculation each month-end.

Step 4: FILTER + SORT — Building an Auto-Ranked Chase List

The most useful thing you can do with a dynamic ageing report is turn it into a prioritised chase list — the invoices most worth calling today, ranked automatically:

This combines FILTER (invoices over 60 days, excluding anything already flagged as disputed) with SORT (ranked by outstanding balance, largest first). The result is a live, self-updating list — collections staff open one sheet and always see the current highest-priority accounts at the top, with no manual sorting required after each payment posts.

Version Compatibility and the #CALC! Trap

FILTER requires Excel 365 or Excel 2021. On older versions, the formula returns a #NAME? error rather than a result. There's a second, less obvious issue worth flagging: if a FILTER formula's result array would spill into cells that already contain data, Excel returns a #SPILL! error rather than overwriting anything — which is a deliberate safeguard, but it means inserting a new column into a "protected" spill range downstream will break every report built on it. Leave clear blank space below and to the right of any FILTER formula before building on top of it.

Three Mistakes That Break FILTER-Based Reports

When FILTER Isn't Enough

This approach handles a single-entity ledger comfortably into the tens of thousands of rows. Once you're consolidating ageing across multiple entities with different currencies, or pulling raw exports from several ERPs that need cleaning before any filter logic can run reliably, the transformation work belongs in Power Query ahead of the filtering layer — a decision we cover in a dedicated Power Query vs formulas comparison. For the general case of when to reach for SUMIFS instead, see our SUMIFS for financial analysis guide.

If you'd rather not rebuild the bucket logic, ECL matrix, and chase-list ranking from scratch every time, AR Intelligence Manager has this exact structure — dynamic ageing, IFRS 9 provisioning, and an auto-ranked chase list — already built into the workbook.

Frequently Asked Questions

Conclusion: From Static Snapshots to a Living Aging Report

A pivot table tells you where your receivables stood at the last refresh. FILTER, combined with Boolean AND/OR logic, SORT, and a bucket classification layer, gives you a report that's accurate the moment you open it — no refresh button, no manually rebuilt chase list, no invoice quietly ageing past 90 days without anyone noticing.

Your action plan:

1. 1Convert your invoice ledger to an Excel Table if it isn't already — FILTER's structured references depend on it for automatic range expansion
2. 2Replace static pivot ageing with a FILTER + IFS bucket formula that recalculates against TODAY() rather than a fixed refresh date
3. 3Add the if_empty argument to every FILTER formula to avoid #CALC! errors when a filter genuinely returns zero rows
4. 4Layer SORT on top of FILTER to turn your ageing report into a ranked, self-updating chase list

Once this is running, your ageing report stops being a month-end task and becomes something you can open any morning and trust.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.