How to Count Visible Rows With Criteria in Excel

Why This Task Matters in Excel

Picture a filtered sales report where only the rows for the current month are displayed. You still need to know how many of those visible transactions were paid by credit card, or how many of the displayed projects are marked “High Priority.” In other words, you must apply a criterion (credit-card payments, high priority, “Completed,” employee name, etc.) only to the rows users can see right now.

In business settings, hiding or filtering rows is routine:

• Finance teams collapse prior-year months to focus on the current quarter.
• Operations managers filter tickets to show only “Open” items.
• Marketing analysts hide out-of-scope campaigns when presenting.

In all those cases, decision-makers need counts— but counts that respect the current visible context. A standard COUNTIF or COUNTIFS will happily scan every row, hidden or not, leading to misleading KPIs and bad decisions.

Excel offers great flexibility for visibility-aware calculations because:

• The grid fundamentally supports hiding, grouping, and AutoFilter.
• Functions like SUBTOTAL and AGGREGATE automatically ignore hidden rows.
• Array-calculation engines (legacy CSE and modern dynamic arrays) let you layer multiple tests in one cell.

Failing to master this task means presenting dashboards that contradict what the audience sees, or manually recounting rows every single time filter settings change— an error-prone and time-consuming process. Learning to count visible rows with criteria ties directly into other essential skills: building interactive dashboards, designing refreshable reports, and writing robust, auditable formulas.

Best Excel Approach

The most reliable universal technique combines three ingredients:

1. SUBTOTAL with function number 103 – returns 1 for each visible cell, 0 for hidden.
2. OFFSET (or a direct range reference inside modern BYROW) – evaluates SUBTOTAL row-by-row.
3. SUMPRODUCT (or the dynamic-array equivalent SUM) – multiplies the visibility flag by the criteria test and then adds the results.

Why is this best? Because it works in every modern Excel version from 2007 forward, inside or outside structured tables, with manual hides, outline groups, and AutoFilters. No helper columns are required, and performance is acceptable for thousands of rows.

The classic syntax is:

=SUMPRODUCT(
      SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$100)-ROW($A$2),0)),
      --($B$2:$B$100="Credit Card")
)

Parameter breakdown:

• $A$2:$A$100 – visibility range: any non-blank column inside the dataset.
• 103 – tells SUBTOTAL to count only visible cells and work in array mode.
• OFFSET($A$2,ROW($A$2:$A$100)-ROW($A$2),0) – feeds SUBTOTAL one cell at a time.
• --($B$2:$B$100="Credit Card") – converts TRUE/FALSE into 1/0 for counting.
• SUMPRODUCT multiplies the two arrays row-by-row and adds the results.

If you have Microsoft 365 you can streamline with FILTER + COUNTIF:

=COUNTIF( FILTER(Table1[PaymentMethod],Table1[PaymentMethod]="Credit Card") ,"<>" )

FILTER automatically returns only visible rows when the sheet is filtered, so the count respects visibility without extra gymnastics. However, this is 365-only and doesn’t work against manually hidden rows outside AutoFilter.

Parameters and Inputs

• Visibility Range – Choose a column guaranteed to be filled for every record (an ID or Date column works well). It is used only to check whether a row is visible, so the values themselves do not affect the result.
• Criteria Range – The column you evaluate with your condition. It must be the same size and alignment as the visibility range.
• Criterion Value – Text, number, logical test, or cell reference defining what you want to count. When comparing text, remember that "Apple" is case insensitive in = comparisons; use EXACT if you need case sensitivity.
• Function Number 103 – Do not confuse it with 3. SUBTOTAL(103,...) counts visible text/non-empty cells; 3 counts all cells.
• Optional Multiple Criteria – Wrap each additional test in its own --(range=condition) inside SUMPRODUCT.
• Data Preparation – Ensure no blank rows inside the dataset. Blanks break grouped outlines and sometimes make SUBTOTAL return unexpected zeros.
• Edge Cases – Hidden columns do not matter; only row visibility is evaluated. If the sheet is protected with row hiding disabled, counts will always include all rows.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a simple sales log in [A1:D11]:

1. Apply an AutoFilter (Data ➜ Filter) and filter Region to show only “East.” Rows 3 and 4 stay visible; the rest are hidden.
2. Goal: count how many visible rows were paid by Credit Card.
3. Select an empty cell (H2) and enter:

=SUMPRODUCT(
     SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$6)-ROW($A$2),0)),
     --($D$2:$D$6="Credit Card")
)

4. Press Enter. Excel returns 1. Why?

• SUBTOTAL(103,OFFSET(...)) produces [1,1,0,0,0] because the first two detail rows are visible and the last three are hidden by the filter.
• --($D$2:$D$6="Credit Card") evaluates to [1,0,1,1,0].
• Multiplying pairwise gives [1,0,0,0,0]. The sum is 1.

Common variations:

• Counting multiple payments: wrap the logical test in an OR approach such as --ISNUMBER(MATCH($D$2:$D$6,["Credit Card","Wire"],0)).
• Case-sensitive text: replace "Credit Card" with EXACT($D$2:$D$6,"Credit Card").

Troubleshooting: if you get zero, confirm you included the 103 function number and that the ranges start and end on the same rows.

Example 2: Real-World Application

A project management office (PMO) tracks tasks in a table TasksTbl with columns Date, Project, Owner, Status, Priority. Managers often filter by Owner and Priority to discuss workloads. They want to know how many visible tasks are still “In Progress.”

Dataset: TasksTbl[Status] stores the status; TasksTbl[Date] is always populated and will be the visibility column.

Step-by-step:

1. Convert the range to an Excel Table (Ctrl + T) and name it TasksTbl.
2. Apply filters: Owner = “Zhang”, Priority = “High.”
3. In a summary section, enter:

=SUMPRODUCT(
     SUBTOTAL(103,OFFSET(TasksTbl[Date],ROW(TasksTbl[Date])-ROW(TasksTbl[[#Headers],[Date]]),0)),
     --(TasksTbl[Status]="In Progress")
)

Explanation:

• TasksTbl[Date] gives visibility flags.
• ROW(TasksTbl[Date])-ROW(TasksTbl[[#Headers],[Date]]) converts absolute row numbers into a 0-based offset compatible with OFFSET.
• Because this formula references the table, it survives row additions and deletions without edits.

Result: the number updates instantly whenever anyone changes the filters— perfect for a live dashboard.

Integration: the PMO feeds that cell into a KPI gauge chart for status meetings. They also wrap it in IFERROR so an empty filter context produces 0, not #VALUE!.

Performance: with 10 000 tasks the formula recalculates in under 0.05 seconds on modern hardware. If performance stalls, switch to a helper column approach (see Alternative Methods).

Example 3: Advanced Technique

You need to count visible rows that match two criteria (Region and Payment) and you want the solution to run lightning-fast on a 100 000-row ledger. Helper columns can reduce formula evaluation overhead.

1. Insert a helper column “VisibleFlag” next to the dataset (column G). In G2 enter:

=SUBTOTAL(103,$A2)

Copy down. Each visible row now shows 1; hidden rows return 0.

2. In cell H2 calculate the count:

=SUMPRODUCT( (G2:G100001), --(C2:C100001="East"), --(D2:D100001="Credit Card") )

Because “VisibleFlag” is pre-calculated, SUMPRODUCT multiplies three straightforward arrays, avoiding thousands of OFFSET calls.

3. Hide the helper column or style the font white to keep reports clean.

Edge cases: If the user manually hides a row after the helper column is filled, SUBTOTAL recalculates automatically in modern Excel. For extremely large models, set calculation to Manual and press F9 when ready.

Professional tip: replace SUMPRODUCT with SUMIFS for an even faster 365 solution:

=SUMIFS(G2:G100001,C2:C100001,"East",D2:D100001,"Credit Card")

Because G already stores 1 or 0, SUMIFS produces the same count but parses criteria far more efficiently.

Tips and Best Practices

1. Anchor ranges with absolute references so hiding or inserting columns does not break OFFSET.
2. Keep the visibility column narrow (ID or Date) to minimize memory if you later use helper columns.
3. Prefill blank cells— SUBTOTAL treats completely blank cells as “not visible” in some mixed-filter scenarios.
4. Test formula speed on the full dataset before distributing: array-heavy OFFSET calls can snowball.
5. Document the 103 switch in a comment; colleagues often overlook the difference between 3 and 103.
6. Consider Excel Tables; their structured references self-expand, eliminating range edits when the dataset grows.

Common Mistakes to Avoid

1. Using SUBTOTAL(3,…) instead of 103 – that variant counts hidden rows, so results are inflated. Verify by toggling a filter and confirming the count changes.
2. Mismatched range sizes – if the visibility range is [A2:A100] but the criteria range is [B2:B99], SUMPRODUCT returns #VALUE!. Always select entire, equal-length columns.
3. Forgetting the double unary -- – Omitting it leaves TRUE/FALSE values, and SUMPRODUCT treats them as 0, leading to a total of 0.
4. Referencing entire columns (A:A) in older Excel – Performance degrades because OFFSET touches every possible row. Restrict ranges to the actual data set or use structured tables.
5. Combining manual hides with FILTER – FILTER ignores manual row hiding; don’t assume both visibility methods behave the same.

Alternative Methods

Choose Helper Column + SUMIFS when the dataset exceeds roughly 50 000 rows and you can tolerate an extra column. Prefer Dynamic Arrays for small dashboards targeting Office 365 audiences.

FAQ

When should I use this approach?

Use it whenever your audience will interactively filter or group data and you need the displayed count to update instantly— dashboards, interactive summaries, or printed reports that hide preliminary rows.

Can this work across multiple sheets?

Yes. Make the visibility column reference external sheets, e.g., Sheet1!$A$2:$A$5000. All supporting ranges must reside in the same workbook, lined up row-for-row. Performance is slightly slower crossing sheets.

What are the limitations?

SUBTOTAL ignores filtered rows and manually hidden rows, but not rows hidden through row height zero by VBA unless calculation runs afterward. Array formulas with OFFSET are volatile, recalculating on nearly every change.

How do I handle errors?

Wrap the whole formula in IFERROR( ,0) to return 0 instead of #VALUE! when the dataset is empty or criteria ranges misalign. Debug by evaluating inside Formula ➜ Evaluate Formula to see which component turns to error first.

Does this work in older Excel versions?

Yes back to Excel 2003 with SUMPRODUCT. Structured table references require 2007+. Dynamic-array FILTER requires Office 365 or Excel 2021.

What about performance with large datasets?

• Limit ranges to actual data rows.
• Switch to the helper column pattern so only one SUBTOTAL per row is evaluated.
• Turn off automatic calculation for massive models and recalc on demand.
• Keep volatile functions like OFFSET and INDIRECT to a minimum.

Conclusion

Counting visible rows with criteria is a cornerstone technique for reliable, interactive Excel reports. Mastery lets you deliver numbers that match what people see, tightening trust in your analytics. You have several pathways— from the universally compatible SUMPRODUCT + SUBTOTAL combo to lightning-fast helper columns or slick dynamic-array formulas. Practice these patterns, benchmark them on your real data, and soon you will weave visibility-aware metrics seamlessly into any workbook. Keep exploring related skills like structured references, PivotTables, and Power Query to round out your Excel toolkit and tackle even more complex reporting challenges with confidence.