SUMIFS Formula Not Working? Common Causes and Fixes

If your SUMIFS formula is not working, first check that its sum range and every criteria range cover the same number of rows and columns, then verify the criteria syntax and the underlying cell values. In Excel for Microsoft 365 on Windows, SUMIFS adds values that meet all supplied conditions; a mismatch in range sizes, dates stored as text, or a criterion that does not match the data can make the result wrong or zero.

How SUMIFS works

SUMIFS uses one range for the values to add, followed by pairs of criteria ranges and criteria. The first argument is always the sum range, a detail that is easy to mix up if you are familiar with older functions such as SUMIF, whose argument order is different. Each additional pair tells Excel which cells to test and what condition they must meet. With multiple pairs, a row must satisfy every condition to contribute to the total.

For a simple example, assume regions are in A2:A6, transaction dates are in B2:B6, and sales amounts are in C2:C6. The rows contain East sales of 120 on January 5, East sales of 80 on January 20, West sales of 200 on January 12, East sales of 150 on February 2, and East sales of 50 on January 31, 2025. With that layout, this formula totals East sales made during January 2025:

=SUMIFS(C2:C6,A2:A6,"East",B2:B6,">="&DATE(2025,1,1),B2:B6,"<"&DATE(2025,2,1))

The result is 250: 120 + 80 + 50. The January start is inclusive, while the February 1 boundary is exclusive, so the formula also handles dates that include times. This pattern is generally safer than trying to build a “last day of the month” condition.

Check the argument order and range sizes

The most basic structural problem is placing arguments in the wrong order. The syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], and so on). Make sure the first range contains the numbers you want to add, not the labels or dates being tested. Also check each comma and each opening and closing parenthesis.

The sum range and all criteria ranges must have matching dimensions. For example, a sum range from row 2 through row 100 should not be paired with a criteria range from row 2 through row 99. Such a mismatch can produce a #VALUE! error. Use the same start and end rows for every range, and make sure the ranges are aligned so each criterion is testing the corresponding row.

With regions in A2:A100, sales in C2:C100, and the region to look for in E2, this formula uses matching 99-row ranges:

=SUMIFS($C$2:$C$100,$A$2:$A$100,E2)

If the formula will be copied down, the dollar signs keep the data ranges fixed while E2 changes to E3, E4, and so on. They do not repair mismatched ranges, so inspect the endpoints even when references are absolute.

Find out why the result is zero

A zero result often means the formula is valid but no rows meet every condition. Test the conditions one at a time: temporarily remove later criteria pairs, or compare the total before and after adding each condition. This narrows down which test excludes the expected rows. Check for spelling differences, extra spaces, inconsistent abbreviations, and criteria that refer to the wrong cell.

For a quick baseline, suppose the sales amounts are in C2:C6. This formula adds every numeric amount in that range:

=SUM(C2:C6)

The result is 600. If your SUMIFS result is zero but this baseline is 600, the amounts are present and the problem is likely in a condition or its matching data. If the expected region is East and regions are in A2:A6 with amounts in C2:C6, this single-condition check should return 400:

=SUMIFS(C2:C6,A2:A6,"East")

If it does not, compare the actual region cells with the criterion. SUMIFS comparisons are not case-sensitive, so East and EAST match, but a trailing space or a different spelling can prevent a match. A criterion that references a blank cell can also behave differently from a criterion you meant to supply; confirm the referenced cell actually contains the intended value.

Write criteria with operators and wildcards

For an exact text criterion, a quoted word such as “East” is straightforward. For comparisons, put the operator and value together in a criterion string, or join the operator to a cell reference with the ampersand operator. For example, criteria such as greater than, less than, or not equal to need the operator represented as text. When the value comes from a cell, joining the operator and cell reference is essential; typing a comparison operator outside the criteria string does not create a valid SUMIFS condition.

Suppose scores are in B2:B5 and amounts are in C2:C5. With scores 60, 75, 90, and 55, and amounts 10, 20, 30, and 40 respectively, the formula below adds amounts for scores of at least 70:

=SUMIFS(C2:C5,B2:B5,">=70")

The result is 50, from the rows with scores of 75 and 90. For a criterion based on a cell, assume the threshold is in E2 and the same score and amount ranges are used:

=SUMIFS(C2:C5,B2:B5,">="&E2)

Wildcards can help when text varies. An asterisk matches any sequence of characters, and a question mark matches one character. With category labels in A2:A5 and amounts in C2:C5, this formula sums rows whose category contains the text East:

=SUMIFS(C2:C5,A2:A5,"*East*")

Wildcards are for text matching, not a general substitute for checking numeric or date values. Excel also treats wildcard matching as case-insensitive. If you need an exact text match, use the full criterion rather than adding wildcards.

Fix date criteria and numbers stored as text

Dates are a frequent source of unexpected results because Excel stores genuine dates as serial numbers, while imported or manually entered dates may be text that only looks like a date. A date criterion can match real dates but not text strings that resemble them. Check a suspicious cell by changing its number format or testing whether Excel recognizes it as a date; formatting alone, however, cannot convert text into a real date.

For date periods, use a start date and an exclusive end date. With dates in B2:B100 and amounts in C2:C100, this formula sums amounts dated in January 2025:

=SUMIFS(C2:C100,B2:B100,">="&DATE(2025,1,1),B2:B100,"<"&DATE(2025,2,1))

DATE constructs real date values, avoiding ambiguity between regional date formats such as month/day/year and day/month/year. If you use cells for the start and end dates, confirm those cells contain genuine Excel dates, not text. The exclusive upper boundary includes every time on January 31 when date-time values are present.

Numbers stored as text can cause related confusion. SUMIFS adds numeric values in its sum range; text entries that look numeric are not necessarily added. If the formula finds matching rows but the total is too small, inspect the amount cells for imported text, apostrophes, or inconsistent values. Converting the source data to real numbers is usually better than hiding the issue inside a complicated formula.

Investigate errors, references, and calculation settings

The displayed result can offer a useful clue. #VALUE! commonly points to incompatible range dimensions or an error already present in a relevant cell. #NAME? can indicate a misspelled function or a name Excel does not recognize. A formula displayed literally in the cell may have been entered while the cell was formatted as Text, or Show Formulas may be enabled. Select the cell, check the formula bar, and confirm the entry begins with an equals sign.

Also inspect worksheet references. If the data is on another sheet, the range should include the sheet name, such as Sheet2!A2:A100; sheet names containing spaces need single quotes around the name. When copying formulas, check whether a relative reference shifted unexpectedly. The dollar signs in $A$2:$A$100 lock both the column and row; $A2 locks only the column, and A$2 locks only the row.

If values change but the result does not, check calculation mode. In Excel for Microsoft 365 on Windows, use Formulas > Calculation Options and select Automatic if appropriate. Press F9 to recalculate formulas in an open workbook. On Mac, the Calculation Options command is also on the Formulas tab; keyboard shortcuts can differ by keyboard and system settings, so use the ribbon command if a shortcut does not work. Manual calculation can be intentional in large workbooks, so understand why it is enabled before changing it.

Use a systematic troubleshooting sequence

Rather than rewriting the formula at random, work from the simplest check to the most specific condition. This makes it easier to separate a formula-structure problem from a data-quality problem:

Confirm the sum range is the range of values you intend to add, and verify its endpoints.
Check that every criteria range has the same dimensions and row alignment as the sum range.
Test the formula with one criterion, then add the other criteria pairs one at a time.
Compare each criterion with the source cells, checking spaces, spelling, data types, and referenced cells.
For dates and thresholds, use a genuine date or number and join comparison operators to cell references correctly.
Look for errors in the source data, text-formatted numbers, shifted references, or manual calculation mode.

For a deeper inspection, select the formula cell and choose Formulas > Evaluate Formula. Excel steps through parts of the calculation so you can see how references and criteria are being interpreted. This is useful when the formula is long, but it does not replace checking whether the source values are clean and consistently typed.

Know when SUMIFS is not the right tool

SUMIFS applies AND logic across criteria pairs: every condition must be true for a row to count. A common misconception is that adding two criteria pairs for the same range creates an either/or test. For example, asking the same region range to equal East and equal West at the same time cannot match a single cell. For a small number of alternatives, add separate SUMIFS calculations. If the conditions are more complex, a helper column or another formula approach may be easier to audit.

SUMIFS is a good choice for totals based on several straightforward conditions, such as region plus month or product plus status. It is not a counting function; use COUNTIFS when the goal is to count matching rows rather than add values. A PivotTable may be more convenient when readers need to explore many categories and totals interactively. The best option depends on whether the result needs to be a fixed calculation, a reusable report, or an exploratory summary.

For an either/or total, assume regions are in A2:A6 and amounts are in C2:C6, with the example data above:

=SUMIFS(C2:C6,A2:A6,"East")+SUMIFS(C2:C6,A2:A6,"West")

The result is 600 because it adds all East amounts, 400, and all West amounts, 200. If your alternatives overlap or the formula grows, document the logic clearly and test the result against a small sample of source rows.

Frequently Asked Questions

Why does SUMIFS return zero when matching rows are visible?
Visible matches may differ from the criterion because of extra spaces, a different spelling, text-versus-number values, or dates stored as text. Test one criterion at a time and inspect the actual cell contents, not just how they appear when formatted.

Does SUMIFS care about uppercase and lowercase text?
No. SUMIFS text comparisons are not case-sensitive, so East and EAST match. If capitalization is the only difference you can see, look instead for spaces, other characters, or a reference to the wrong range.

Can SUMIFS use dates from cells?
Yes. A criterion can be built by joining an operator such as >= to a cell containing a genuine Excel date. Confirm the date cell is not text, and use an exclusive end date when a period may include times.

Why does SUMIFS show #VALUE!?
Check whether the sum range and every criteria range have the same dimensions. Also look for errors in referenced cells and confirm the formula arguments are separated and paired correctly.

How do I sum rows that meet either of two conditions?
SUMIFS uses AND logic between criteria pairs. For two separate alternatives, you can add two SUMIFS calculations, provided that doing so does not double-count rows. For more complicated OR logic, consider a helper column or a PivotTable.

Make the formula easier to maintain

Once the formula returns the expected number, make it less fragile. Keep source columns consistent, avoid mixing text and numbers in the same field, and use clear labels for criteria cells. If a report will grow, convert the source range into an Excel table so new rows can be included more naturally; check the structured references Excel creates rather than assuming a copied range will expand. Preserve a small test case with known totals when a calculation is important. Rechecking the result against a few manually verified rows can reveal a logic error that a syntactically valid formula will not flag.

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.