In Excel, SUMIF adds values that meet a condition, while SUBTOTAL can recalculate over rows left visible by a filter; neither function alone does both jobs. To total only matching visible rows in Excel for Microsoft 365 on Windows, use =SUMPRODUCT(SUBTOTAL(109,OFFSET($B$2,ROW($B$2:$B$10)-ROW($B$2),0,1)),--($A$2:$A$10=D2)), with categories in A2:A10, amounts in B2:B10, and the category to match in D2.
What SUMIF and SUBTOTAL each do
SUMIF has three arguments: the range to check, the criterion, and the range to add. It is useful for a conditional total such as sales for one region, regardless of whether the source rows are currently filtered. With categories in A2:A10, amounts in B2:B10, and the requested category in D2, enter =SUMIF($A$2:$A$10,D2,$B$2:$B$10). If D2 contains East, the formula adds every East amount in the specified range.
SUBTOTAL performs a function such as SUM over a range and treats filtered-out rows differently from ordinary SUM formulas. With amounts in B2:B10, =SUBTOTAL(9,B2:B10) adds the visible values and excludes rows hidden by an AutoFilter. The function number 9 means SUM; 109 also means SUM, but additionally excludes rows hidden manually. Neither version accepts a category criterion the way SUMIF does.
That difference matters when someone asks for a “subtotal formula with SUMIF.” They may mean either a conditional total for all records, which SUMIF already provides, or a conditional total that changes as filters hide rows. The second request needs a visibility-aware construction.
Why wrapping one function in the other is not enough
Putting SUMIF inside SUBTOTAL does not make SUMIF examine which individual source rows are visible. SUMIF calculates one result using its criteria and sum ranges; the hidden-row information has already been lost in that single result. SUBTOTAL cannot then selectively remove the hidden records from it. Similarly, a plain SUMIF outside SUBTOTAL still includes matching records hidden by a filter.
This is not a conflict between functions. Each one is doing its assigned job: SUMIF applies a condition, and SUBTOTAL responds to filtered visibility. To combine those behaviors, Excel needs to evaluate visibility for each amount and then keep only the rows whose category meets the condition. The SUMPRODUCT formula in the introduction performs those two tests row by row.
How the visible-row formula works
Assume column A contains categories, column B contains amounts, and D2 contains the category to total. The ranges in the formula must cover the same rows. Enter this formula in a cell outside the filtered data:
With categories in A2:A10, amounts in B2:B10, and the criterion in D2, use =SUMPRODUCT(SUBTOTAL(109,OFFSET($B$2,ROW($B$2:$B$10)-ROW($B$2),0,1)),--($A$2:$A$10=D2)).
ROW($B$2:$B$10)-ROW($B$2)generates offsets of 0 through 8, one for each amount cell.OFFSETturns those offsets into individual references in column B.SUBTOTAL(109,...)returns each amount when its row is visible and zero when the row is filtered out or manually hidden.($A$2:$A$10=D2)checks each category. The double unary,--, converts TRUE and FALSE results to 1 and 0.SUMPRODUCTmultiplies the visibility results by the category tests and adds the remaining values.
The formula does not need Ctrl+Shift+Enter in Microsoft 365. It updates when the filter changes. The result is a numeric total, not a displayed set of subtotal rows.
Worked example with a filter
Suppose rows 2:10 contain these category-and-amount pairs: East 120, West 90, East 75, North 60, East 40, West 110, East 30, North 80, and West 50. Put East in D2. The ordinary SUMIF result is 265: 120 + 75 + 40 + 30. It includes all four East records whether or not some rows are hidden.
Now assume there is also a Status field in column C, and a filter on that field leaves rows 2, 3, 5, 8, and 10 visible. The visible East amounts are 120 and 30. The SUMPRODUCT formula returns 150. In this example, =SUBTOTAL(109,B2:B10) returns 350, the sum of all amounts in the visible rows, while the category-aware formula returns only the visible East amounts. This illustrates why the two formulas answer different questions.
Apply and check an Excel filter
In Excel for Microsoft 365 on Windows, click a cell in the data and choose Data > Filter. You can also toggle AutoFilter with Ctrl+Shift+L. Use the dropdown in the Status column to select the records you want visible; the formula then responds to the filtered rows. Keep the formula cell outside the filtered list so that it is not hidden along with the source records.
On Mac, use Data > Filter; keyboard shortcuts can differ with Excel version and keyboard settings. In either version, confirm that the filter applies to the entire data range, including every field used in the calculation. If headers are missing or the range is split, Excel may filter only part of the records, and the displayed subtotal will no longer represent the intended set.
A quick check is to compare the result with a small manual addition of visible matching rows. Change the filter to another selection and verify that the total changes as expected. Clearing the filter should make the conditional visible-row formula agree with SUMIF for the same category, assuming the source range and data are clean.
Choose the right formula for the job
- Use SUMIF when you want a category total across all records, even when a filter is applied. It is shorter, easier to audit, and generally the best choice for a standard conditional total.
- Use SUBTOTAL when you want a sum of visible numeric cells and do not need a category condition.
- Use SUMPRODUCT with SUBTOTAL and OFFSET when the answer must satisfy both a category test and current row visibility.
- Use a PivotTable when the task involves several categories, multiple conditions, grouped dates, or repeated summaries. A PivotTable is often easier to maintain than a long formula for that kind of report.
For multiple conditions across all records, SUMIFS may be more readable than SUMPRODUCT, but it also does not automatically ignore filtered-out rows. Visibility is the deciding requirement: a conditional formula is not filter-aware simply because the source table has dropdown filters.
Limits and common problems
The OFFSET-based construction is volatile: Excel recalculates it when many workbook changes occur, which can affect performance if copied across thousands of rows or used with very large ranges. Keep the row range bounded to the actual data rather than using full-column references. When new rows are added, extend the formula ranges or set up a clearly defined data range so the added records are included.
Check that the category and amount ranges have identical dimensions and start and end on the same rows. A mismatch can cause errors or incorrect calculations. Amount cells should contain numbers; text that looks like a number may not be added. Category labels must also match the criterion. Extra spaces, inconsistent spelling, or values stored differently can make a row fail the category test.
The formula uses 109, so it excludes both filter-hidden rows and rows hidden manually. If you want manually hidden rows included while still excluding rows removed by a filter, change the function number to 9 in the formula. Do not change the number if manually hidden records should be omitted too. Also note that subtotal formulas placed inside the source range can complicate totals; keep the result in a separate summary area.
Alternatives for larger or recurring reports
If the report has many categories or users need to explore totals, a PivotTable can summarize the data by category and allow filtering through its field controls. It avoids maintaining a separate conditional formula for every category. However, a PivotTable’s filter and refresh behavior is distinct from an AutoFilter applied to the raw rows; refresh it when source data changes, and check which fields are being filtered before comparing its result with a worksheet formula.
A helper column is another option when transparency matters more than compactness. For example, a helper calculation can mark whether each record is currently visible, then a separate conditional sum can use that marker. Building a reliable visibility marker still requires a visibility-aware calculation, so it is not automatically simpler; it can nevertheless make a complex report easier to inspect. For a simple single-condition filtered total, the SUMPRODUCT formula is usually the direct worksheet solution.
Frequently Asked Questions
Can SUMIF ignore rows hidden by a filter?
No. SUMIF evaluates its criteria and sum range without treating AutoFilter-hidden rows specially. Use the visibility-aware SUMPRODUCT formula when filtered visibility must affect a conditional total.
Does SUBTOTAL support a condition such as East?
Not directly. SUBTOTAL can calculate a visible-row aggregate, but it has no criteria argument like SUMIF. Combine per-row visibility with a category test, or use a PivotTable for a broader report.
What is the difference between SUBTOTAL 9 and 109?
Both specify SUM and ignore rows excluded by a filter. Code 109 also ignores rows hidden manually; code 9 includes manually hidden rows. Choose according to what “visible” should mean in your workbook.
Will the formula update when I change the filter?
Yes. SUBTOTAL responds to AutoFilter visibility, so the result recalculates as rows are filtered or shown. The formula does not respond to a category selection unless that condition is included, as it is through D2 in the example.
Can I use SUMIFS instead of SUMIF?
You can use SUMIFS for multiple criteria across all records, but it does not by itself exclude filtered rows. If the total must change with row visibility, use a visibility-aware approach or a PivotTable configured for the report.
Related
- Line of Balance Schedule: How It Works on Projects
- How to Subtotal in Numbers and Summarize by Category
- Java Enums: How to Define and Use Them in Your Code
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- IF Then for Dates in Excel: Formulas and ExamplesTo use IF with dates in Excel, compare a cell containing a real date with another date or…
- COUNTIF Drop-Down in Excel: Count SelectionsTo count how often an Excel drop-down choice appears, use =COUNTIF($B$2:$B$100,D2) when responses are in B2:B100 and the…
- Excel FILTER Function for Duplicates: Find RepeatsWith IDs in A2:A100 and full records in A2:D100, enter =FILTER(A2:D100,COUNTIF(A2:A100,A2:A100)>1,"No duplicates") to return every row whose ID…
- Create an Invoice in Excel With Formulas and TotalsCreate an invoice in Excel by setting up columns for descriptions, quantities, unit prices, and line amounts, then…
- Excel SUM OFFSET: Create Flexible, Expanding TotalsIn Excel, combine SUM and OFFSET to total a range whose size or position can change: for example,…
- Excel COUNTIF: Count Cells That Aren’t ErrorsCOUNTIF does not have a reliable criterion for “is not an error.” To count cells that contain no…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.