How to Count Visible Rows in Excel After Filtering

How-to basics · Updated November 20, 2026

To count entries that remain visible after you filter a list, use Excel’s SUBTOTAL function: for a column with a value in every record, enter =SUBTOTAL(103,C2:C7) in a cell outside the filtered list. The count updates when you change the filter. Before starting, make sure your data has a header row and choose a column that is filled in for every record you want to count.

This guide is for Excel for Microsoft 365 on Windows. The same function works in Excel for Mac and Excel for the web, although ribbon labels or their locations may vary slightly.

What You’ll Need

  • An Excel worksheet with a header row and a continuous data range, or an Excel table.
  • A column that contains a value for each record you want to include, such as an order number, employee name, or case ID.
  • A blank cell outside the data range for the result. If you use a table, you can instead turn on its Total Row.

Step-by-Step

  1. Check the data and choose the column to count. Confirm that each column has a clear header and that there are no completely blank rows splitting the list. Pick a column with a value in every record. An ID column is usually a good choice; a notes column may contain blanks and undercount rows. If your list has headers in row 1 and records in rows 2 through 7, the records occupy rows 2:7.
  2. Turn on filtering. Click a cell in the data range. On Windows, select Data > Filter. Excel adds a drop-down arrow to each header. You can also select Home > Sort & Filter > Filter. In Excel for Mac or the web, use the Filter command on the Data tab; the label is the same, though the ribbon layout can differ. If the data is already an Excel table, its header filter arrows are normally available.
  3. Enter a SUBTOTAL formula outside the filtered rows. For example, suppose A1:C7 contains a small list with headers in row 1, and C2:C7 contains a nonblank order ID for each record. In a blank cell outside the list, enter =SUBTOTAL(103,C2:C7). The first argument, 103, tells Excel to count nonblank cells while excluding rows hidden by a filter. The range starts at row 2 so the header is not included. Press Enter to show the current count. Keep the result cell outside the range being filtered so it does not get hidden or become part of the data.
  4. Apply a filter and check the result. Open the drop-down arrow for a field such as Region, clear Select All, select East, and click OK. If East appears in three records and each has an order ID in column C, the formula returns 3. Select another region or add a filter in another column; the result recalculates to count the records still visible after all active filters.
  5. Choose the right counting version. Use =SUBTOTAL(103,C2:C7) when the chosen column can contain text or numbers and you want to count nonblank cells. If the column contains only numbers, use =SUBTOTAL(102,C2:C7) instead; 102 counts numeric cells only. With the example above, where three East records have numeric IDs, either formula returns 3. If those IDs are text, 102 returns 0, while 103 returns 3.
  6. Verify what the count represents. SUBTOTAL counts qualifying cells in the range, not abstract records. If a visible record has a blank in the counted column, it is not included. To count records reliably, use a consistently populated identifier column. If you want a count of visible numeric amounts rather than records, use the amount column with 102, knowing that blank or text amounts will not count.
  7. For a table, consider using its Total Row. Click inside the table, open the Table Design tab, and turn on Total Row. In the new bottom row, click the cell under the column you want to count, open its drop-down list, and choose Count. Excel uses a SUBTOTAL-based calculation for table totals, so the displayed count responds to filtering. Check that the selected column is populated in every record. If the tab is named differently in your version or platform, look for the table-specific Design tab after selecting a table cell.
  8. Change or clear filters to update the count. Use a header drop-down to select different values, or choose Clear Filter From followed by the field name to remove one filter. To remove all filters, use Data > Clear where available, or open each header menu and clear its filter. SUBTOTAL recalculates as the visible rows change; you do not need to re-enter the formula.

Common Mistakes

  • Using COUNT or COUNTA by itself. =COUNT(C2:C7) counts numbers, and =COUNTA(C2:C7) counts nonblank cells, but neither is designed to ignore rows hidden by a filter. They can therefore include records you cannot currently see. Use SUBTOTAL for a filter-responsive count.
  • Counting the header. A range such as C1:C7 includes the heading in C1. Since the header remains visible while records are filtered, a nonblank-count formula can add one to the result. Start at the first data row, such as C2.
  • Choosing a column with missing values. A visible row with a blank in the selected column will not count with 102 or 103. Use a reliable identifier column or fill in the missing values if the count is meant to represent records.
  • Confusing cells with rows. SUBTOTAL counts cells that meet its rule. Counting a text field with 102, or a number-only field with 103 when blanks matter, can produce a result that does not match your intention. Match the function number and column to the kind of values you have.
  • Putting the result inside the filtered range. The formula cell may itself be filtered, included in the source range, or mistaken for a data record. Put it in a separate area, or use a table’s Total Row.

Tips

  • Use 103 for a general record count. It counts nonblank cells and ignores both filtered-out rows and rows hidden manually. The related code 3 also counts nonblank cells but includes rows hidden manually. Both ignore rows removed from view by filtering.
  • Remember what the function codes mean. Codes 2 and 102 count numbers; codes 3 and 103 count nonblank cells. The 101–111 versions ignore manually hidden rows, while the 1–11 versions do not. For a filtered list where manually hidden rows should not count either, 102 or 103 is usually the clearest choice.
  • Use a table for lists that grow. Tables expand when you add records and keep filter controls with the headers. A Total Row can make the count easy to find without placing a formula in the worksheet area above or below the list.
  • Use multiple filters when the count needs multiple conditions. For example, filter Region to East and Status to Open. SUBTOTAL then counts qualifying visible entries in the selected column. This is often simpler and more reliable than trying to make COUNTIF account for hidden rows.
  • Label the result. Add a nearby label such as Visible records so another person can tell that the number changes with the filter, rather than representing the full dataset.

Troubleshooting

The count does not change when you filter. Check that the formula uses SUBTOTAL, not COUNT or COUNTA, and that its range includes the data rows. Also make sure you filtered the same list covered by the formula. If the formula references a different column or a separate copy of the data, filtering the list will not produce the expected count.

The count is one too high. Look for a header included in the range. Change a range beginning at C1 to begin at C2 if row 1 contains the heading. Also check whether the result cell was accidentally included in the range.

The count is lower than the visible row total. Inspect the counted column for blanks. A blank cell is not counted, even when its row is visible. Switch to a consistently populated ID column. If you use 102, check that the values are real numbers and not text that only looks numeric; use 103 for a nonblank text-or-number field.

The count is higher than the number of visible rows. Confirm that the range does not include extra rows outside the list, including a second header, a total, or unrelated entries. Check the formula’s function number: SUBTOTAL 2 or 3 includes manually hidden rows, while 102 or 103 excludes them. Filtered-out rows are excluded by either version.

Excel displays the formula instead of a result. Make sure the cell is not formatted as Text. Change its number format to General, then select the formula cell, press F2, and press Enter. Also confirm that the formula begins with an equals sign and that Show Formulas is not turned on.

The formula shows an error. Check the range for a typo and make sure parentheses are paired. In standard Microsoft 365 settings, use a comma between the function number and range. If your regional settings require another list separator, Excel may use a semicolon instead; follow the separator Excel accepts in your installation.

Frequently Asked Questions

Does SUBTOTAL count only rows that remain visible after filtering?
Yes. It ignores rows removed from view by a filter. With 102 or 103, it also ignores rows hidden manually. It counts qualifying cells in your chosen range, so a blank in that column still will not be counted.

Should I use 102 or 103?
Use 102 to count numeric cells only. Use 103 to count any nonblank cell, including text. For a record count, 103 is generally suitable when every record has a populated identifier, regardless of whether that identifier is stored as text or as a number.

Can I count filtered rows with COUNTIF?
COUNTIF normally counts matching cells even in rows hidden by a filter. If you need the number of visible records matching a condition, apply that condition as a filter too, then use SUBTOTAL on a populated column. This makes the visible rows and the count rule match.

Will the count update when I change a filter?
Yes. SUBTOTAL recalculates when you change which rows are visible through the filter. If the display seems unchanged, verify that the filter applies to the formula’s data range and that the chosen counting column has values in the records you expect.

Can I use this in Excel for Mac or Excel for the web?
Yes. SUBTOTAL is available in both. Use the Filter command on the Data tab, then enter the same formula in a cell outside the list. Excel for Mac may arrange ribbon controls differently, but the counting behavior is the same.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.