Function Problems

How to Count Overdue Invoices Using Excel Slicer Formulas

Nimra Malik Sep 28, 2026 870 views

Question details

The user needs a formula to count the number of overdue invoices that remain visible after applying filters or slicers to an Excel table.

How to Count Filtered Overdue Invoices with Excel Slicer Formulas

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering a table of debtors using slicers and attempting to count only the currently visible records that have exceeded their payment terms.
Observed behavior: The standard SUBTOTAL function counts filtered rows but cannot evaluate specific conditions (like checking if days overdue is greater than 60), while COUNTIF handles conditions but includes hidden/filtered rows.

Before you start

Ensure your invoice data is formatted as an Excel Table and that you have identified the specific column references for your calculation, such as the days overdue column.

Solution 1Recommended

Use an Array Formula with SUBTOTAL and OFFSET

This advanced method evaluates criteria exclusively on visible rows, allowing you to count items that meet a specific overdue condition within a filtered dataset.

Because functions like COUNTIF cannot distinguish between hidden and visible rows, combining SUBTOTAL with OFFSET forces Excel to evaluate each visible row individually against your overdue criteria.

Identify your table references

Determine the name of your table and the overdue column. For this example, we use the table 'DEBTORS' and the column 'DAYS O/DUE'.

Enter the array formula

Select the cell for your result and input the following formula: =SUM(IFERROR((SUBTOTAL(3,OFFSET(J6,ROW(DEBTORS[DAYS O/DUE])-ROW(J6),)))*(DEBTORS[DAYS O/DUE]>60)*ISNUMBER(DEBTORS[DAYS O/DUE]),0)).

Adjust references to your specific sheet

Change 'J6' to the cell reference of the very first data cell in your 'DAYS O/DUE' column, and change '>60' to your required overdue threshold.

Calculate the formula

Press Enter to evaluate. If you are using an older version of Excel, you may need to press Ctrl+Shift+Enter to calculate it as an array formula.

Use an Array Formula with SUBTOTAL and OFFSET

Formula explanation: The OFFSET function loops through each row of the column, SUBTOTAL(3, ...) checks if the row is visible, and the multiplication acts as a boolean AND operator to apply the >60 days condition.

Solution 2

Use a Helper Column and Standard SUBTOTAL

If complex array formulas are difficult to maintain, you can simplify the process by calculating the overdue status directly in a new table column.

Advanced Data Analysis Tool

Track Overdue Invoices Effortlessly with WPS Spreadsheet

WPS Office offers a robust, free spreadsheet application that fully supports advanced array formulas, slicers, and dynamic table functions. You can easily manage financial data using the exact same SUBTOTAL and OFFSET formulas you use in Microsoft Excel.

1. Open your invoice tracker: Launch WPS Spreadsheet and open your existing invoice workbook.
2. Format data as a Table: Select your data range and press Ctrl+T to convert it into a Table, enabling dynamic referencing.
3. Insert Slicers for filtering: Go to the Insert tab and select 'Slicer'. Check the fields you want to filter by, such as Client or Month.
4. Apply the summary formula: Paste the SUBTOTAL and OFFSET array formula into your summary cell.
5. Filter dynamically: Click the slicer buttons to filter your data and watch the overdue invoice count update in real-time.

100% format and formula compatibility with Microsoft Excel (.xlsx files)Free and lightweight Office alternative that runs smoothly on most devicesBuilt-in support for data slicers and advanced array formulas for dynamic trackingFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use COUNTIF for filtered data?

The COUNTIF and COUNTIFS functions evaluate ranges globally and cannot detect whether a row is hidden by a filter or a slicer. To count only visible cells based on a condition, you must use functions explicitly designed to ignore hidden rows, such as SUBTOTAL or AGGREGATE, combined with array mechanics.

What does the number 3 mean in the SUBTOTAL formula?

In the SUBTOTAL function, the first argument determines the type of calculation. The number 3 represents the COUNTA function, which counts non-empty cells in the visible range. Alternatively, using 103 does the same but also ignores manually hidden rows, not just filtered ones.

Do I need to press Ctrl+Shift+Enter for this formula?

If you are using an older version of Excel that does not support dynamic arrays, yes, you must use Ctrl+Shift+Enter to evaluate the combination of SUBTOTAL, OFFSET, and conditional multiplication properly. In newer versions (like Microsoft 365) or modern alternatives like WPS Office, pressing Enter is usually sufficient.

Excel & Data Problems