SUMIFS Not Working With Multiple Criteria? Common Fixes

When SUMIFS is not working with multiple criteria, first check that its arguments are in the right order and that every criteria range matches the size and shape of the sum range. SUMIFS applies multiple criteria as AND conditions, so each listed condition must be true for a row to be included; for OR conditions, use separate SUMIFS calculations and add their results.

How SUMIFS handles multiple criteria

SUMIFS adds values from a sum range only when the corresponding cells meet every condition you specify. Its argument order is the range to add, followed by a criteria range and its criterion, repeated as needed. The criteria ranges do not have to be adjacent, but they should line up row by row with the sum range.

For example, suppose a small sales table has a region in column A, a product category in column B, and a sale amount in column C. A request to total Hardware sales in the East region means both conditions must match on the same row. With region data in A2:A6, category data in B2:B6, and amounts in C2:C6, this formula returns 170 for the example rows below:

East, Hardware, 120
East, Software, 80
West, Hardware, 150
West, Software, 200
East, Hardware, 50

=SUMIFS($C$2:$C$6,$A$2:$A$6,"East",$B$2:$B$6,"Hardware")

The result is 120 + 50 = 170. This distinction matters: SUMIFS does not normally interpret multiple criteria as alternatives. It looks for rows satisfying all the supplied criteria together.

Check the argument order and range sizes

A common mistake is using the SUMIF argument order in a SUMIFS formula. SUMIF starts with a criteria range, while SUMIFS starts with the sum range. A formula can sometimes be syntactically valid yet refer to the wrong columns, so check each argument rather than relying on how the formula looks at a glance.

All criteria ranges should have the same number of rows and columns as the sum range. If the sum range is C2:C100, use corresponding ranges such as A2:A100 and B2:B100, not A2:A99 or B3:B100. A mismatch can produce an error or an unexpected total. Also confirm that the ranges begin and end on the same records; equally sized but offset ranges can silently pair a sale with the wrong region or category.

Confirm the first argument contains the values you intend to add.
Pair each criteria range with its criterion in the correct order.
Make every range cover the same rows, including the first data row and last data row.
Use dollar signs when copying a formula if its ranges should stay fixed.

In Microsoft 365 for Windows, click the formula cell and inspect its references in the formula bar, or select a range reference in the formula bar to see the referenced cells highlighted. The same formula-entry and review approach works in Excel for Mac and Excel for the web, although ribbon placement and keyboard shortcuts can differ.

AND versus OR criteria

Multiple criteria in one SUMIFS are combined with AND logic. For instance, Region = East and Category = Hardware includes only rows where both are true. If the intended meaning is Region = East or Region = West, two SUMIFS calculations can be added. With regions in A2:A6 and amounts in C2:C6, the following returns 600 when the five example amounts total 600:

=SUM(SUMIFS($C$2:$C$6,$A$2:$A$6,"East"),SUMIFS($C$2:$C$6,$A$2:$A$6,"West"))

This works because each row in the example belongs to either East or West, so the two subtotals cover all five rows. Be careful if your alternatives can overlap: adding results for two criteria that both match the same records can count those records twice. In that situation, define the intended groups carefully or use a method that avoids overlapping matches.

For multiple acceptable values in the same column, another option in Microsoft 365 is to use an array constant inside SUMIFS and wrap it in SUM. However, the separate-calculation approach is often easier to audit, and it makes the OR logic explicit to someone reviewing the workbook.

Build criteria for numbers and operators

Criteria that use comparison operators must be entered as text criteria. When the threshold is stored in a cell, join the operator text to that cell reference with the ampersand. For example, with amounts in C2:C6, regions in A2:A6, and a minimum value in E2, this formula adds East sales that are at least the threshold. If E2 contains 100, the result is 120:

=SUMIFS($C$2:$C$6,$A$2:$A$6,"East",$C$2:$C$6,">="&E2)

In the HTML formula above, the ampersand is displayed as its HTML entity; Excel formula syntax uses the ordinary ampersand character. In a worksheet, enter =SUMIFS($C$2:$C$6,$A$2:$A$6,"East",$C$2:$C$6,">="&E2) with the normal & operator. Common operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>).

Check whether the values being summed are genuinely numeric. A value that looks like 120 but is stored as text may not be added as expected. Select a sample cell and inspect its content, or try converting imported data to numbers before troubleshooting the criteria. Also make sure the criterion is comparing the intended units: a threshold of 100 is different from a text label such as “100 units.”

Make date criteria reliable

Dates are a frequent source of zero results because cells that look like dates may actually contain text, or the criterion may include a time component that is not visible in the cell display. Use real Excel dates and build date boundaries with DATE rather than relying on ambiguous text such as “1/2/26,” whose interpretation can vary with regional settings.

Assume transaction dates are in A2:A4 and amounts are in B2:B4: January 4, 2026 has amount 40, January 17 has amount 60, and February 2 has amount 90. To total January transactions, use a start boundary that includes January 1 and an exclusive boundary for February 1. This returns 100:

=SUMIFS($B$2:$B$4,$A$2:$A$4,">="&DATE(2026,1,1),$A$2:$A$4,"<"&DATE(2026,2,1))

Again, enter ordinary & and < characters in Excel; HTML encodes them above for display. Using a less-than next-month boundary is useful when the source dates might include times. A criterion of “less than or equal to January 31” can exclude records from late on January 31 if the cells include time values and the boundary is interpreted as midnight.

Use text criteria and wildcards correctly

Text matching is generally not case-sensitive in SUMIFS. Thus, “East” and “east” normally match the same text. Extra spaces, nonprinting characters, spelling differences, and inconsistent labels are more likely causes of a missed match. Data pasted from websites or exported systems can contain leading or trailing spaces that are hard to notice. Compare a problem cell with a known-good value, and consider cleaning the source data or using a helper column with normalized text.

SUMIFS supports wildcards for text criteria. An asterisk represents any number of characters, and a question mark represents one character. A tilde escapes a wildcard when you want to search for a literal asterisk or question mark. For example, if product descriptions are in A2:A4 and amounts are in B2:B4, and the rows are “Green apple” (12), “Apple juice” (8), and “Pineapple” (5), this criterion finds all three and returns 25:

=SUMIFS($B$2:$B$4,$A$2:$A$4,"*apple*")

The asterisks allow text before or after “apple.” Because matching is not case-sensitive, the capital A in “Apple juice” does not prevent a match. If you need exact text, omit the wildcards; if a cell must contain a literal wildcard character, escape it with a tilde.

Diagnose zero results and formula errors

A result of zero does not necessarily mean the formula is broken. It can mean that no row satisfies every criterion, that the sum cells contain text rather than numbers, or that one of the criteria is not the value you think it is. Test criteria one at a time by temporarily removing a pair, or count matching records with a filter so you can see whether any rows meet the conditions.

For an error or unexpected output, use this checklist:

Look for a missing quote, parenthesis, comma, or criteria-range/criterion pair.
Check whether the formula points to the intended worksheet and cells.
Check for merged cells, hidden rows, or filters that make the visible data misleading. SUMIFS does not limit its calculation to visible rows just because a filter is applied.
Verify that criteria values have matching data types: a real date should be compared with a real date, and numeric thresholds should not be mistaken for text.
Look for blank cells or inconsistent labels where you expected a match.

In Excel for Microsoft 365 on Windows, Evaluate Formula is available from Formulas > Formula Auditing > Evaluate Formula and can help trace how a formula is evaluated. The feature is also available in desktop Excel for Mac, though its exact ribbon layout can vary by release. For a quick visual check, use the filter dropdowns on the source table to isolate the same criteria and compare the visible values with the formula result.

When to choose SUMIFS or another approach

SUMIFS is a good fit for a straightforward total based on several conditions, such as sales by region, category, and date period. It is easier to maintain than manually adding many individual cell references, and it recalculates when the source values change. If the formula has many alternative groups, complicated text normalization, or a need to show the matching records, consider whether a PivotTable or helper columns would make the workbook clearer.

A PivotTable can summarize totals by fields such as region and category without writing a long formula. Helper columns are useful when source data needs consistent cleanup, such as removing spaces or assigning a standardized category. SUMPRODUCT can handle some more complex array-style logic, but it is not automatically a better replacement: it can be harder for a colleague to interpret and can require extra care with range sizes and performance. Choose the simplest method that expresses the business rule accurately.

Also distinguish a formula problem from a calculation-mode issue. If a workbook is set to manual calculation, edited inputs may not refresh results immediately. In desktop Excel for Windows, check Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. Excel for Mac has a similar calculation setting. Avoid changing calculation mode without considering other workbook formulas, since it affects more than one SUMIFS cell.

Frequently Asked Questions

Can SUMIFS use more than two criteria?
Yes. Add another criteria-range and criterion pair for each additional condition. Keep the ranges aligned with the sum range and remember that the conditions are combined with AND logic.

Why does SUMIFS return zero when the criteria look right?
Check for hidden spaces, mismatched data types, misaligned ranges, or a condition that excludes every row. Dates stored as text and numbers stored as text are especially common causes.

Does SUMIFS support OR criteria?
Not by treating separate criteria pairs as alternatives. Add separate SUMIFS results for the alternatives, and make sure those groups do not overlap if duplicate counting would be a problem.

Can SUMIFS match part of a text value?
Yes. Use * for any number of characters or ? for one character. For example, an asterisk before and after a word matches text containing that word. Wildcard matching is not case-sensitive.

Why does SUMIFS include filtered-out rows?
SUMIFS evaluates the referenced cells, including rows hidden by a filter. If the total must respond to visible rows only, use a different approach designed for filtered data rather than expecting SUMIFS to ignore hidden records.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.