Google Sheets SUMIF: How to Exclude Subtotal Rows Safely

To keep subtotal rows out of a Google Sheets SUMIF result, add a row-type column such as “Detail” or “Subtotal” and use SUMIFS to require both the category and “Detail.” SUMIF has only one condition, so it cannot independently test the category and exclude subtotal rows based on a second column.

Why SUMIF includes subtotal rows

SUMIF checks a criteria range for a match and adds the values from the corresponding cells in a sum range. It does not know whether a matching cell contains an ordinary entry, a manually typed subtotal, or a formula that calculates a subtotal. If a subtotal row repeats a category name used by your SUMIF criterion, that row matches too.

For example, suppose column A contains category names and column B contains amounts. Rows 2 and 3 are apple entries worth 10 and 12, and row 4 is an Apples subtotal worth 22. A SUMIF that searches for “Apples” adds 10, 12, and 22, returning 44 rather than the detail total of 22. The subtotal is not being counted because SUMIF misunderstands arithmetic; it is counted because its row meets the stated condition.

This is a common source of inflated results in reports that place subtotals among the underlying records. SUMIF does not inspect how a number was calculated or infer a hierarchy from indentation, formatting, borders, or bold text. The formula needs a condition that distinguishes the rows you intend to include.

Use SUMIFS with a row-type condition

The most dependable fix is to identify detail rows separately from subtotal rows, then add that distinction as a second condition. In the example below, A2:A7 contains category names, B2:B7 contains amounts, and C2:C7 contains the row type. The two apple detail rows have “Detail” in column C, while the subtotal row has “Subtotal.”

With categories in A2:A7, amounts in B2:B7, and row types in C2:C7, this formula returns 22 for Apples because it sums only rows matching both criteria:

=SUMIFS(B2:B7,A2:A7,"Apples",C2:C7,"Detail")

SUMIFS takes the sum range first, followed by pairs of criteria ranges and criteria. Here, the first pair selects Apples and the second pair selects Detail. In the same example, an orange detail amount of 8 and another of 7 produce 15; an orange subtotal row is excluded for the same reason.

Make sure every criteria range has the same dimensions as the sum range. If the sum range covers rows 2 through 100, the category and row-type ranges should also cover rows 2 through 100. Mismatched ranges can lead to errors or unexpected results, especially after a sheet is edited.

Add a reliable row-type marker

A helper column is usually easier to maintain than trying to infer which rows are subtotals from their numbers. Add a header such as “Row type” and enter a consistent label—“Detail” or “Subtotal,” for example—on each applicable row. Then build the SUMIFS condition around the label you want to include.

For a small report, you can type the markers manually. If a report is produced repeatedly, make the marker part of the source-data process, or generate the report from a separate detail table. The important point is that the marker represents the role of the row, not whether its amount happens to equal the sum of nearby values.

Use one spelling and capitalization consistently, such as “Detail.”
Put the marker on every relevant row, including subtotal rows added later.
Keep headings and notes outside the data rows covered by the formula, or give them a different row-type label.
If several kinds of summary rows exist, use clear labels such as “Detail,” “Category subtotal,” and “Grand total,” then choose the label that matches your reporting goal.

Labels do not have to be in a dedicated helper column if the data already has an equivalent field. For example, a transaction table may already distinguish “Transaction” from “Summary.” Reusing a meaningful field avoids duplicate maintenance, as long as its values accurately identify the rows to include.

Keep detail data separate from report subtotals

If you control the sheet layout, the cleanest design is often to keep raw detail records in one range or tab and put presentation subtotals elsewhere. Formulas that summarize a detail-only source do not need to work around summary rows in the first place. This also makes it easier to sort, filter, append records, and audit where a total came from.

For a simple example, assume A2:A5 contains detail categories and B2:B5 contains detail amounts, with no subtotal rows in those ranges. The two Apples records are 10 and 12. This SUMIF returns 22:

=SUMIF(A2:A5,"Apples",B2:B5)

If the data grows, use a range that covers the expected detail rows or maintain the source as a consistently structured table. In Google Sheets, a whole-column reference can be convenient, but it may also include titles, notes, or other content placed in that column. A well-defined source range reduces the chance that a later layout change quietly alters the total.

A related trap is using a wildcard criterion. If category cells contain labels such as “Apples” and “Apples subtotal,” a criterion like “Apples*” can match both. An exact criterion for “Apples” will not match “Apples subtotal,” but the best solution is still to make category and row type separate fields rather than encoding two meanings in one label.

When SUBTOTAL is useful—and when it is not

Google Sheets has a SUBTOTAL function for calculations over a range, including totals that respond to filtered data. It can also avoid double-counting nested subtotals when those subtotal rows are themselves calculated with SUBTOTAL. This is useful for a report total across grouped sections, but it is not a direct replacement for a conditional SUMIF: SUBTOTAL does not select rows by a category criterion.

Assume B2:B7 contains detail amounts in rows 2, 3, 5, and 6, with B4 and B7 containing subtotal formulas for their respective groups. If B4 and B7 use SUBTOTAL, then an overall SUBTOTAL across B2:B7 ignores those nested subtotal results and returns the detail total of 37:

=SUBTOTAL(9,B2:B7)

The result is 10 + 12 + 8 + 7 = 37. This behavior depends on the subtotal rows containing SUBTOTAL formulas. If the subtotal amounts are typed as fixed numbers, the outer SUBTOTAL cannot identify them as nested subtotals and may count them. Also, this formula adds the range as a whole; it does not answer “How much is Apples?” on its own.

Filtered rows and hidden rows are a separate issue

Readers sometimes expect a SUMIF result to change when they filter a list. A standard SUMIF evaluates matching cells in its referenced ranges; filtering the sheet is not the same as adding a condition to the formula. If the requirement is to sum only visible rows, SUBTOTAL is designed for range-based calculations that respond to filters. That still does not automatically combine visibility with a category condition in the same straightforward way as SUMIF combines category matching.

Be clear about what “remove subtotals” means in your sheet. Excluding subtotal records from a category total calls for a row-type condition or a detail-only source range. Ignoring rows hidden by a filter calls for a visibility-aware approach. If both requirements apply, test the chosen layout against filtered and unfiltered data; do not assume that a formula meeting one requirement also meets the other.

Common mistakes to check

Using SUMIF with only the category condition: it cannot also test a row-type column. Use SUMIFS when you need multiple conditions.
Putting subtotal amounts in the detail data: a formula cannot reliably know that a number is a subtotal unless the data provides that information.
Using inconsistent helper labels: “Detail,” “Details,” and a blank cell are different criteria values. Check for spelling differences and missing markers.
Using broad wildcards: a criterion such as “Apples*” may include labels that begin with Apples, including summary labels.
Including headers or notes: check that the formula ranges cover the intended records and that the header row is not being treated as data.
Assuming SUBTOTAL filters by category: it does not. It calculates over a range; category selection is a separate requirement.

When a result looks too large, inspect the rows that meet the category criterion before changing the arithmetic. A quick audit of the matched rows often reveals that a subtotal, grand total, or repeated header is being included. Then decide whether to correct the source layout, add a row-type marker, or tighten the referenced range.

Which approach should you choose?

Use SUMIFS with a row-type marker when detail and subtotal rows must remain together and you need a conditional total by category. It is explicit and scales to additional criteria, such as date, department, or status. Use a detail-only source range when you can separate the underlying records from the presentation report; it is often the simplest design to audit.

Use SUBTOTAL when your goal is a range total that handles filtered rows or nested SUBTOTAL formulas, not when you need to identify a category by itself. If the existing sheet has no marker and subtotal labels are indistinguishable from detail labels, there is no dependable way for SUMIF alone to infer which matching amount should be excluded. Add that information or revise the data structure before relying on the result.

Google Sheets and Excel both provide SUMIF, SUMIFS, and SUBTOTAL, so the core formula logic is similar in each program. This guidance uses Google Sheets in a web browser; Excel may offer different table tools and interface paths, but changing programs does not make SUMIF infer which rows are subtotals.

Frequently Asked Questions

Can SUMIF exclude subtotal rows by itself?
Not when the subtotal row matches the same criterion as the detail rows. SUMIF accepts one condition. Add a row-type condition with SUMIFS, or point SUMIF at a range containing detail records only.

Does SUMIFS ignore rows containing formulas?
No. SUMIFS evaluates the criteria ranges and adds corresponding values in the sum range. It does not exclude a row just because its value comes from a formula. A row-type marker or a source range without subtotals is needed.

Will SUBTOTAL remove subtotal rows from a SUMIF result?
No. SUBTOTAL does not change what SUMIF matches. An outer SUBTOTAL can ignore nested SUBTOTAL calculations when totaling a range, but that is a different calculation and does not select a category.

Why does my total double when I add subtotals?
If the subtotal amount is included in the same range as the detail amounts, a matching SUMIF may add both the detail values and their subtotal. Exclude summary rows through a second condition or keep them out of the source range.

Can I exclude rows by their bold text or formatting?
Not with an ordinary SUMIF criterion. Formatting is not a reliable row-type condition for this formula. Use an explicit value in a helper column or a separate detail-data range so the inclusion rule is visible and maintainable.

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.