Calculation Issues

How to Count Completed Items in Filtered Excel Columns

Amos Gikunda Oct 9, 2026 869 views

Question details

The user needs an Excel formula to count cells containing the word Completed across columns C and D. The count must update automatically to ignore hidden rows when a filter is applied to another column.

How to Count Text Across Multiple Filtered Columns in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Filtering a dataset by a specific criteria (e.g., district in column B) and needing an accurate count of status updates spanning multiple columns.
Observed behavior: Standard formulas like COUNTIF do not ignore filtered rows, and combining SUBTOTAL with OFFSET usually only handles a single column at a time.

Before you start

Ensure your data is organized as a continuous range without merged cells. If possible, convert your data range into an official Excel Table to make your formulas easier to read and maintain.

Solution 1Recommended

Use SUMPRODUCT, SUBTOTAL, and OFFSET

This advanced formula calculates the total instances of a specific word across multiple columns while dynamically ignoring rows hidden by a filter.

By nesting OFFSET inside SUBTOTAL, you create an array of visible rows. SUMPRODUCT then evaluates this visibility array against your target text across columns C and D.

Identify your data ranges

Determine the exact rows your data occupies. For example, assume your data runs from row 2 to row 100 in columns C and D.

Enter the dynamic counting formula

Select the cell where you want the result to appear and enter the following formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET(C2:C100,ROW(C2:C100)-MIN(ROW(C2:C100)),0,1)),(C2:C100="Completed")+(D2:D100="Completed"))

Apply a filter to test

Go to the Data tab, apply a filter to your District column (Column B), and select a specific district. The count will instantly update to reflect only the visible rows.

Formula Breakdown: The addition (+) between the two conditions acts as an operator, allowing the formula to check both column C and column D simultaneously for the word Completed.

Solution 2

Filter and Count using Power Query

Power Query provides a visual, non-formula way to filter your data and count specific values across multiple columns.

Advanced Data Analysis

Count Filtered Data Effortlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT, SUBTOTAL, and OFFSET. You can easily manage complex datasets, filter multiple conditions, and calculate dynamic results with an intuitive interface.

1. Open your dataset: Launch WPS Spreadsheet and open your existing workbook containing the filtered data.
2. Apply a filter: Highlight your header row and navigate to Data > Filter on the top ribbon.
3. Insert the dynamic formula: Type the combined SUMPRODUCT and SUBTOTAL array formula into your designated total cell.
4. Filter and review: Filter by the District column and instantly view the dynamically updated count of Completed items.

Fully compatible with Microsoft Excel formulas (.xlsx)High-performance calculation engine for large datasetsBuilt-in Data Filter and Advanced Filter toolsFree and lightweight alternative for everyday office tasks

Frequently Asked Questions

Why doesn't COUNTIF work with filtered rows?

The standard COUNTIF and COUNTIFS functions evaluate all cells in a specified range, regardless of whether they are hidden by a filter. To count only visible cells, you must use functions designed to ignore hidden rows, such as SUBTOTAL or AGGREGATE.

Can I use AGGREGATE instead of SUBTOTAL for this formula?

Yes, AGGREGATE can be used similarly to SUBTOTAL to ignore hidden rows (using function number 3 for COUNTA and option 5 to ignore hidden rows). However, combining it with array operations across multiple columns still requires it to be nested within SUMPRODUCT to evaluate properly.

How do I adapt this formula if my data is in an official Excel Table?

If your data is formatted as a Table (e.g., named Table1), you can replace standard cell references like C2:C100 with structured references like Table1[Column C] inside the formula. This makes the formula automatically expand as new rows are added to the table.

Excel & Data Problems