How to Use SUBTOTAL with COUNTIF in Excel

Formulas & functions · Updated November 22, 2026

Combining SUBTOTAL with COUNTIF lets you count cells that meet a condition while ignoring rows hidden by filtering. This is useful for dashboards or reports where you want the count to update automatically as you filter. The trick is to use SUBTOTAL with function number 3 (COUNTA) on a helper column that flags matching rows, then wrap that with COUNTIF to count only the flags. Here’s the exact formula: =COUNTIF(helper_range,1) where the helper column uses =SUBTOTAL(3,cell).

What You’ll Need

  • Microsoft Excel 365, Excel 2021, Excel 2019, or Excel 2016 (the steps work in all these versions).
  • A worksheet with data in a table or range, including at least one column with text or numbers you want to count conditionally.
  • Basic familiarity with entering formulas and using AutoFilter (Data > Filter).

Step-by-Step

  1. Prepare your data. Suppose you have a list of sales orders in columns A (Product) and B (Region), with headers in row 1. For this example, we’ll count how many orders are for “Widget” in the visible (filtered) rows. Make sure your data has a header row and no entirely blank rows.
  2. Add a helper column. Insert a new column to the right of your data, say column C. In C1, type a header like “Visible”.
  3. Enter the SUBTOTAL formula in the helper column. In C2, enter: =SUBTOTAL(3,A2). The 3 tells Excel to use COUNTA, which counts non-empty cells. This returns 1 if the row is visible (not filtered out) and 0 if the row is hidden. Drag the formula down to the last row of your data.
  4. Verify the helper column works. Apply a filter (Data > Filter) and check a column, e.g., Region = “East”. Rows that are hidden will show 0 in column C, visible rows show 1. If you clear the filter, all rows show 1.
  5. Write the COUNTIF formula. In a cell outside your data (e.g., E2), enter: =COUNTIF(C2:C100,1). This counts the number of 1s in the helper column, which equals the number of visible rows. But this alone counts all visible rows, not just those matching a condition.
  6. Combine with a condition. To count only visible rows where column A = “Widget”, you need to modify the helper column. Instead of just checking visibility, make the helper column return 1 only if the row is visible AND the condition is true. Change the formula in C2 to: =IF(AND(SUBTOTAL(3,A2)=1,A2="Widget"),1,0). Drag down. Now the helper column shows 1 only for visible “Widget” rows.
  7. Final count formula. In E2, keep =COUNTIF(C2:C100,1). This now gives the count of visible rows where Product is “Widget”. For example, if your data has 10 Widget rows total, but filtering hides 4 of them, the formula returns 6.
  8. Test it. Apply different filters and watch the count update. Clear all filters to see the total count of matching rows.

Common Mistakes

  • Using COUNTIF directly on the data range – This counts all rows, ignoring filters. For example, =COUNTIF(A:A,"Widget") gives the total, not the visible count.
  • Using SUBTOTAL with function number 2 or 109 – These are for COUNT, not COUNTA. If your cells contain text, use 3 or 103. For numbers, 2 or 102 works, but 3/103 is safer.
  • Not dragging the helper formula down far enough – If you add rows later, the helper column won’t cover them, so the count will be wrong.
  • Putting the COUNTIF formula in the same column as the helper – This can cause circular references. Put it in a separate column.
  • Using =SUBTOTAL(3,A2) on a cell that is part of the filter criteria – That’s fine, but if you filter by that same column, the SUBTOTAL still works because it checks the row’s visibility, not the cell’s value.

Tips

  • Use a table – Convert your data to a Table (Ctrl+T) so the helper column formula auto-fills as you add rows. The formula will propagate automatically.
  • Hide the helper column – Right-click the column header and select “Hide” to keep your worksheet clean. The formulas still work.
  • For multiple conditions – Extend the IF function with AND: =IF(AND(SUBTOTAL(3,A2)=1,A2="Widget",B2="East"),1,0).
  • Use function number 103 instead of 3 – Both work, but 103 is for ignoring manually hidden rows as well as filtered rows. Use 3 for filter-only, 103 for both.
  • Alternative with SUMPRODUCT – If you prefer a single formula without a helper column, use: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A100="Widget")). This is more advanced but works in Excel 365 and earlier versions.

Troubleshooting

  • The count doesn’t change when I filter. Check that the helper column formula uses SUBTOTAL and not a static value. Also ensure the helper column is within the filtered range (i.e., not outside the data area).
  • The count returns 0 even when there are matching visible rows. Verify that the condition text matches exactly, including case (Excel is case-insensitive for text, but check for extra spaces). Use TRIM if needed.
  • #VALUE! error in the helper column. This usually means the SUBTOTAL function is referencing a range that includes the formula cell itself. Make sure the SUBTOTAL references a single cell (e.g., A2) not a range.
  • #NAME? error. This indicates a typo in the function name. Check that you typed SUBTOTAL and COUNTIF correctly.
  • The count includes hidden rows that are not filtered. If you manually hide rows (right-click > Hide), use function number 103 instead of 3 in the SUBTOTAL part.
  • When I clear the filter, the count is wrong. Make sure the helper column formula is dragged down to cover all data rows. If you added rows after setting up, the new rows may not have the formula.

Frequently Asked Questions

  • Can I use COUNTIF directly with SUBTOTAL? No, SUBTOTAL doesn’t accept COUNTIF as an argument. You need the helper column approach or an array formula like SUMPRODUCT.
  • Does this work in Google Sheets? Yes, but the syntax is slightly different. In Google Sheets, you can use =SUBTOTAL(3,A2) and =COUNTIF, but the SUMPRODUCT alternative may require array handling.
  • Can I count unique visible values? Yes, but that’s more complex. You’d need to combine SUBTOTAL with SUMPRODUCT and COUNTIF on the helper column, or use a dynamic array formula like =COUNTA(UNIQUE(FILTER(A2:A100,SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1))))) in Excel 365.
  • Why does my helper column show 0 for visible rows? This happens if the SUBTOTAL function is referencing a range that includes the formula cell itself (circular reference). Change the reference to a single cell.
  • Is there a way to avoid the helper column? Yes, use the SUMPRODUCT formula mentioned in Tips. It’s more advanced but works in a single cell.
  • Does this work with PivotTables? No, PivotTables have their own filtering and counting methods. This technique is for regular ranges or Tables.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.