SUMIFS Not Summing Correctly? Excel Causes and Fixes

SUMIFS usually returns an unexpected total because its ranges do not line up, a criterion does not match the underlying data, or values that look like numbers or dates are stored differently than Excel expects. Check the range dimensions first, then verify each criterion against the actual cell contents; these checks resolve most SUMIFS problems in Excel for Microsoft 365 on Windows.

How SUMIFS evaluates a formula

SUMIFS adds numbers from a sum range only when the corresponding cells meet every condition you specify. Its argument order is important: the range to add comes first, followed by one or more pairs of a criteria range and a criterion. Multiple criteria are combined with AND logic: a row must satisfy all of them to contribute to the total.

For example, imagine a table with regions in A2:A5, sales representatives in B2:B5, and sales amounts in C2:C5. The four rows are North/Ana/120, South/Ben/90, North/Ana/80, and North/Ben/50. With that layout, this formula sums sales where the region is North and the representative is Ana: =SUMIFS(C2:C5,A2:A5,"North",B2:B5,"Ana"). The result is 200, because 120 plus 80 equals 200.

That example also gives you a useful diagnostic: if the total should be 200 but is zero or a different number, check whether the formula refers to those intended columns and rows, and whether the cell contents really match the criteria.

Check that all ranges have matching dimensions

The sum range and every criteria range must have the same shape and align row by row. If the sum range is C2:C100, the criteria ranges should cover the corresponding records, such as A2:A100 and B2:B100. Using A3:A100 by mistake shifts the comparisons by one row; using A2:A99 leaves out a record. Inconsistent range sizes can also cause Excel to return an error.

When building a formula, select ranges from the same first and last data rows. If you add records later, make sure the ranges expand together. Excel Tables can make this easier: convert the data to a table with Insert > Table, then use structured references so the formula can refer to named columns and include new table rows automatically.

Also check that the sum range is actually the column you intend to total. A formula can be syntactically valid yet point to a quantity column instead of an amount column. Trace the colored range outlines while editing the formula, or click a range reference in the formula bar to see which cells it selects.

Review criteria spelling and operator syntax

Text criteria generally need to match the cell contents, including spaces and punctuation. Extra spaces at the beginning or end of a label can make values that look identical on screen fail to match. SUMIFS text matching is not case-sensitive, so North and NORTH match, but a misspelling or an added space does not.

When a criterion includes an operator, put the operator and value together in a quoted criterion, or join the operator to a cell reference with the ampersand. For example, to mean greater than the number in E1, the criterion must be constructed as “>”&E1, rather than treating the cell reference as part of quoted text. Likewise, a literal criterion such as “>100” means greater than 100; it does not mean “match the characters greater than 100.”

With regions in A2:A5 and sales in C2:C5 from the example table, this formula sums North sales greater than 100: =SUMIFS(C2:C5,A2:A5,"North",C2:C5,">100"). The result is 120. The 80 and 50 amounts are below the threshold, and the South sale is excluded by the region condition.

For a criterion stored in a cell, check the cell’s value and type. A formula that joins an operator to a text-formatted number may not behave as intended. Make sure the threshold is a numeric value, not text that only looks numeric.

Look for numbers and dates stored as text

Cells can display 100 or a date while holding text rather than a number or a true Excel date serial. This often happens after importing a CSV, copying data from a website, or pasting values from another system. Text numbers in the sum range are not added as numeric amounts, and text dates in a criteria range may not match date criteria.

To inspect a suspicious cell, select it and check the formula bar and number format; formatting alone does not prove that the value is numeric. You can also test a single cell with ISNUMBER, or try sorting the column and looking for inconsistent alignment or unexpected order. Those clues are useful, but conversion is the actual fix. Depending on the source, use the warning icon’s Convert to Number option, Text to Columns, or a carefully checked helper column that converts the imported values.

Dates need particular care because Excel stores real dates as serial numbers and may display them in different regional formats. If a cell contains a date plus a time, a criterion for an exact date may exclude it: midnight on that date is not the same value as 2:00 p.m. A reliable way to sum a whole day is to use a lower bound at the start of the day and an exclusive upper bound at the start of the next day.

Suppose A2:A4 contains real dates 1/1/2026, 1/15/2026, and 2/1/2026, and B2:B4 contains 100, 70, and 40. With dates in A2:A4 and amounts in B2:B4, this formula sums January 2026: =SUMIFS(B2:B4,A2:A4,">="&DATE(2026,1,1),A2:A4,"<"&DATE(2026,2,1)). It returns 170, because 100 plus 70 falls in January and the February amount is outside the range. The exclusive upper bound also includes January dates with times later than midnight.

Understand wildcards, blanks, and spaces

For text criteria, an asterisk matches any sequence of characters and a question mark matches one character. This helps when labels contain predictable prefixes or suffixes: a criterion such as North* can match North, North-East, or North region. It can also create accidental matches if you expect an exact label but use a wildcard.

If the text you want to match contains an actual asterisk or question mark, precede that character with a tilde so Excel treats it literally. A tilde itself can also be escaped with another tilde. When a criterion seems too broad or too narrow, inspect the source labels and decide whether you need an exact match, a prefix, or a wildcard pattern.

Blank criteria can be useful, but distinguish an actually empty cell from a cell containing a space. A space is text, not an empty cell, and it can make a supposedly blank record fail to match. Cleaning imported labels with TRIM can remove ordinary leading and trailing spaces; some imported nonbreaking spaces need a separate cleanup step. Check a few representative cells before applying a cleanup across the whole column.

Remember that criteria use AND logic

Each criteria pair in SUMIFS narrows the set of qualifying rows. A formula for region North and representative Ana returns rows meeting both conditions; it does not add rows for North or Ana. This is a frequent source of confusion when the desired rule is an either-or condition.

For an OR total, add separate SUMIFS results for each condition when the groups are mutually exclusive. If the groups can overlap, adding separate totals may count a row twice, so define the intended rule before choosing a formula. For more complex combinations of AND and OR, a helper column can make the rule visible and easier to audit; SUMPRODUCT is another option for some custom calculations, but it is less straightforward to maintain than SUMIFS.

Also note that SUMIFS does not care whether matching rows are hidden by a filter. It includes qualifying records that are hidden or filtered out. If you need a total that changes according to visible rows only, SUMIFS alone is not the right tool; use an approach designed to account for filtered visibility.

Debug the formula systematically

Rather than rewriting the whole formula, isolate the condition that is excluding the expected rows. Start with the smallest version that should return a known total, then add criteria one at a time. This reveals whether the problem lies in the sum range, one criterion, or the interaction between criteria.

Confirm the expected rows and calculate their amounts manually for a small sample.
Check that the sum range and criteria ranges start and end on the same rows.
Test the simplest criterion by itself, then add the remaining criteria one pair at a time.
Inspect cells that should match for extra spaces, text numbers, text dates, and hidden time values.
Check for errors in referenced cells and confirm that the formula points to the intended worksheet and columns.

For a visible audit, add a temporary helper column that marks whether each row meets the conditions, then compare the marked rows with the total. Remove the helper column when finished if it is not useful to keep. This is often faster than trying to infer the cause from a single unexpected result.

Check calculation and workbook behavior

If the source data has changed but the result has not, check Excel's calculation setting. On Windows, select Formulas > Calculation Options > Automatic. In automatic calculation mode, Excel normally recalculates formulas when relevant values change. If the workbook is in Manual mode, press F9 to recalculate formulas; on Mac, the corresponding recalculation shortcut is usually Fn+F9, depending on the keyboard settings.

Calculation mode affects the workbook, not the meaning of SUMIFS. A stale result can therefore look like a criteria problem even when the formula itself is sound. If only one result looks wrong, inspect its references and inputs first; if many formulas are stale, check calculation mode and whether the workbook has finished recalculating.

Full-column references are allowed, but they can make a large workbook slower, especially when many formulas recalculate across extensive data. Where practical, use bounded ranges or an Excel Table. Avoid changing ranges merely to improve speed until you have confirmed that doing so will not omit new records.

When to use an alternative

SUMIFS is a good choice for adding values that meet several straightforward conditions. SUMIF handles one condition, while SUMIFS handles multiple criteria pairs. If the goal is to count matching rows rather than add amounts, COUNTIFS is usually the more direct function. If the rule involves complicated OR logic, calculations on transformed values, or visible rows only, consider a helper column, a PivotTable, or a different formula approach suited to that requirement.

Switching functions will not fix mismatched ranges or values stored as text. Before replacing SUMIFS, make sure the intended business rule is clear and the source data is consistent. A simpler, auditable formula is usually preferable to a more elaborate one that hides the real issue.

Frequently Asked Questions

Why does SUMIFS return zero when matching rows are visible?
Usually, one or more criteria do not match the stored values, even if the cells look alike. Check spaces, text-versus-number differences, date times, and the selected ranges. Also confirm that the criteria are being combined with AND logic as intended.

Does SUMIFS include hidden or filtered-out rows?
Yes. SUMIFS evaluates cell values and criteria; it does not exclude rows just because a filter hides them. Use a method that accounts for visible rows if the total must change with the filter.

Can SUMIFS match dates that include times?
Yes, but an exact date criterion can miss values that contain a time component. For a whole-day total, use a start-of-day lower bound and an exclusive start-of-the-next-day upper bound, as in the January example.

Why does SUMIFS return the wrong total instead of zero?
A range may be shifted, a wildcard may match extra labels, or the criteria may include more rows than intended. Check which records qualify one condition at a time, and verify that the sum range contains the correct amounts.

Is SUMIFS case-sensitive?
No. SUMIFS text criteria are not case-sensitive, so capitalization alone will not distinguish two labels. If case-sensitive matching matters, use a different formula approach designed for that requirement.

Can I use SUMIFS with more than one condition?
Yes. Add a criteria-range and criterion pair for each additional condition. Every pair must be satisfied for a row to contribute to the result, and each range should align with the sum range.

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.