Google Sheets COUNTIF: Exclude Subtotal Rows Correctly

To keep subtotal rows out of a Google Sheets count, use COUNTIFS with a condition that rejects the subtotal marker; COUNTIF does not recognize subtotal rows automatically. If the totals are generated by a pivot table, turn off Show totals for the relevant row field in the pivot table editor instead.

Why COUNTIF counts subtotal rows

COUNTIF checks whether each cell in a range matches a criterion. It does not know whether a row represents a transaction, a subtotal, a heading, or a manually entered total. If a subtotal row contains a value that matches your criterion, it is eligible to be counted just like any other row.

This is different from the SUBTOTAL function. Despite the similar names, COUNTIF and SUBTOTAL solve separate problems: COUNTIF counts cells matching a condition, while SUBTOTAL calculates a summary over a range. Neither function can infer the meaning of a row merely from how it looks.

For example, a sheet may contain order rows followed by a row labeled “Subtotal.” If that row repeats “East” in the region column, a count of East values will include it unless the formula has another condition to exclude that row. The reliable fix depends on how subtotal rows are marked: text labels, formulas, or pivot-table settings call for different approaches.

Exclude rows marked Subtotal

The simplest approach is to add a condition for the column that identifies subtotal rows. Suppose column A contains row labels, column B contains regions, and the data occupies rows 2 through 100. The row label is “Subtotal” on subtotal lines, and regular data rows have a nonblank label. To count East records but not subtotal rows, use this formula:

With row labels in A2:A100 and regions in B2:B100, enter:

=COUNTIFS(A2:A100,"<>Subtotal",A2:A100,"<>",B2:B100,"East")

The first condition rejects rows whose label is exactly “Subtotal.” The second rejects blank labels, which is useful because a “not equal to Subtotal” condition alone may allow blank cells to qualify. The last condition counts only rows where the region is East. If the sample contains three East data rows, one East subtotal, and some blank rows, the result is 3.

If subtotal labels vary, such as “East Subtotal” or “Subtotal – East,” use a wildcard condition to exclude any label containing the word Subtotal:

With row labels in A2:A100 and regions in B2:B100, use:

=COUNTIFS(A2:A100,"<>*Subtotal*",A2:A100,"<>",B2:B100,"East")

The asterisks match any text before or after “Subtotal.” Google Sheets criteria are not case-sensitive for this kind of text match, so labels such as “SUBTOTAL” are also excluded. Choose the exact-match version if legitimate data labels might contain the word subtotal and should still count.

When subtotal rows are identified by formulas

Sometimes subtotal rows have no special text label; instead, their total cells contain formulas such as SUM. A dependable option is to create a helper column that marks which rows are regular records. This is especially useful when subtotal rows are created by formulas and the row labels are inconsistent.

Assume each data record has a value in column C, while subtotal rows also have a formula in C. With the first row in row 2, column D available as a helper, put this formula in D2 and fill it down:

=NOT(ISFORMULA(C2))

This returns TRUE when C2 is not a formula and FALSE when it is. Then, if regions are in B2:B100 and the helper results are in D2:D100, count East records marked TRUE:

=COUNTIFS(B2:B100,"East",D2:D100,TRUE)

For example, if there are four East rows but one has a subtotal formula in column C, this formula returns 3. The method only works if formula status genuinely distinguishes subtotal rows from records. If ordinary records also use formulas in C, those rows will be marked FALSE and excluded too. In that case, test the column or condition that uniquely identifies totals, or add a manual row-type field such as “Record” and “Subtotal.”

A helper column is often easier to audit than a long all-in-one formula. You can inspect the TRUE and FALSE flags, spot rows classified unexpectedly, and reuse the same flag in several summaries.

Remove pivot-table subtotals at the source

If the unwanted lines appear inside a Google Sheets pivot table, changing a COUNTIF formula may treat the symptom rather than fix the display. Pivot tables have their own totals setting. Click a cell in the pivot table to open the pivot table editor, then find the relevant field under Rows in the editor. Turn off Show totals for that field. If more than one row field has totals enabled, check each field; a nested pivot table can display totals at multiple grouping levels.

Removing displayed totals does not delete the source records. It changes the pivot table’s presentation and summary output. If another formula counts cells in the pivot table’s output range, changing the totals setting can also change the shape or contents of that range, so review any formulas that refer to it.

For a durable count, it is usually better to count the original data table rather than the pivot output. Pivot tables are designed to summarize and rearrange records; their output can change when fields, filters, or grouping settings change. A formula that refers directly to the source rows is less likely to count a displayed total as though it were a record.

How filters and hidden rows affect the count

A filter view or ordinary filter can hide rows on screen, but COUNTIF still evaluates cells in its specified range, including rows hidden by a filter. Hiding a subtotal row therefore does not make it disappear from a COUNTIF result. The formula’s criteria must exclude that row, or the data must be arranged so that the formula counts only records.

This is a common source of confusion because some spreadsheet summaries respond to filtered rows differently from COUNTIF. The fact that a row is not visible is not the same as the fact that it is absent from the range. If the required result is “count only currently visible records,” make that a separate requirement and choose a method designed around filter visibility; do not assume that a normal COUNTIF follows the filter.

Likewise, sorting or grouping rows does not give subtotal lines a special status. A row remains part of the range whether it is expanded, collapsed, or visually separated, unless the formula or data structure specifically distinguishes it.

Choose the right exclusion method

Before editing a formula, identify what makes a subtotal row different. A clear row marker is usually the best solution, but not every sheet has one. Use the approach that matches the actual structure:

A consistent text label: Add a COUNTIFS condition that excludes that label. Use a wildcard only when subtotal labels vary and matching any label containing “Subtotal” is safe.
A formula only in subtotal cells: Use a helper column with ISFORMULA, provided regular records do not also contain formulas in the checked column.
A pivot-table total: Turn off Show totals for the appropriate row field in the pivot table editor.
No consistent difference: Add a type or status column to the source data. A formula cannot reliably infer that a numeric row is a subtotal if it looks the same as a regular record.

For future sheets, keep raw records in one clean table and put summaries in a separate area or pivot table. Avoid inserting manually calculated subtotal lines between source records. Separating data from presentation reduces accidental double-counting and makes sorting, filtering, and formula ranges easier to maintain.

Common formula problems

If the result is still too high, check that the exclusion condition refers to the column that actually identifies subtotal rows. A label may appear in column A while the count is being performed on column B; both ranges must cover the same rows in the same order. In COUNTIFS, every criteria range must have dimensions that align with the other criteria ranges.

Check the spelling and spacing of labels as well. An exact criterion for “Subtotal” does not exclude “Subtotal ” with a trailing space or “East Subtotal.” Use a wildcard match for variations, or clean inconsistent labels. If you use a wildcard, make sure ordinary entries cannot accidentally include the same keyword.

Also confirm that the count condition matches the data type. A cell containing the text “East” is not the same as a cell containing a differently spelled or spaced value. For numeric conditions, a subtotal may be counted because its number satisfies the same threshold as regular values; add a row-type exclusion rather than trying to guess from the number.

Google Sheets formulas use commas between arguments in many locales, but some spreadsheet locales use semicolons. If Sheets reports a formula parse error, check the spreadsheet’s locale and replace argument separators as required. The functions and criteria remain the same.

COUNTIF compared with COUNTIFS and SUBTOTAL

Use COUNTIF when one condition is enough, such as counting every cell equal to “East.” Use COUNTIFS when the count needs multiple conditions, such as region equals East and row label is not Subtotal. Google Sheets supports both functions; the second is not a special “ignore subtotal” version of the first, but it can express the extra exclusion condition clearly.

Use SUBTOTAL when you want a supported summary calculation over a range, often one that responds to filtered data. It does not replace an arbitrary conditional count just because its name resembles COUNTIF. For the specific task of counting records while excluding identified subtotal rows, a clear marker plus COUNTIFS is usually easier to understand and verify.

Excel has similarly named COUNTIF, COUNTIFS, and SUBTOTAL functions, but this guidance uses Google Sheets in a web browser. The basic criteria logic is comparable, while pivot-table editor controls and some filter-related behavior can differ between spreadsheet products. Follow the Sheets menu and sidebar labels shown above when working in Google Sheets.

Frequently Asked Questions

Does COUNTIF automatically ignore subtotal rows?
No. COUNTIF evaluates every cell in its range against its criterion. Add a condition that excludes a subtotal marker, use a helper flag, or count the original records instead.

Can I exclude a subtotal row that says East Subtotal?
Yes. Use a COUNTIFS condition with a wildcard exclusion such as "<>*Subtotal*" against the label column, along with the region condition. Confirm that no legitimate label containing “Subtotal” should be counted.

Will hiding a subtotal row remove it from COUNTIF?
No. A row hidden by a filter remains within the formula’s range, so COUNTIF can still count it. Explicitly exclude the row or use a method suited to counting visible records.

How do I remove subtotal rows from a Google Sheets pivot table?
Click the pivot table, find the relevant field under Rows in the pivot table editor, and turn off Show totals. If totals appear at more than one grouping level, check the setting for each row field.

What if subtotal rows have no label?
If formulas uniquely identify those rows, use a helper column with ISFORMULA and count only rows flagged as records. Otherwise add a row-type marker; without a consistent distinction, a formula cannot reliably identify which values are subtotals.

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.