SUMIFS Excel Formula Not Working? Fix the Cause

If SUMIFS is not working, first check its argument order, matching range sizes, and criteria: Excel expects =SUMIFS(sum_range,criteria_range1,criteria1,...), and every criteria range must line up with the sum range. A result of 0 often means no cells meet the criteria exactly; an error usually points to formula syntax or mismatched ranges.

How SUMIFS works

SUMIFS adds values from one range only when corresponding cells meet every condition you specify. The first argument is always the range to add. After that, you provide a criteria range and a criterion, then repeat that pair for each additional condition. Conditions are combined with AND logic: a row must satisfy all of them to contribute to the total.

For example, imagine A2:A6 contains North, South, North, North, South; B2:B6 contains 120, 80, 150, 60, 90; and C2:C6 contains Paid, Pending, Paid, Pending, Paid. With regions in A, amounts in B, and statuses in C, this formula adds amounts for rows that are both North and Paid:

=SUMIFS(B2:B6,A2:A6,"North",C2:C6,"Paid")

The result is 270: the matching amounts are 120 and 150. The 60 is excluded because its status is Pending. If you expected 330, check whether you meant to include all North rows rather than only North rows marked Paid.

SUMIFS is available in current desktop Excel, including Microsoft 365 for Windows. If your regional settings use semicolons as formula separators, Excel may display or require semicolons instead of commas. Use the separator expected by your installation; do not mix the two styles within one formula.

Check the argument order and range sizes

A frequent mistake is arranging the arguments like SUMIF. SUMIF takes a criteria range and criterion before its optional sum range, while SUMIFS puts the sum range first. A formula copied from a SUMIF example can therefore refer to the wrong columns when converted without changing the order.

Confirm the ranges row by row. If the sum range is B2:B100, each criteria range should cover the corresponding rows, such as A2:A100 or C2:C100—not A2:A99 or C:C. SUMIFS requires its sum and criteria ranges to have the same dimensions. Inconsistent range sizes can produce an error or a result that does not reflect the records you intended to evaluate.

Check that the first range contains the numbers you want to add.
Check that each criterion is paired with the correct criteria range.
Check that all ranges start and end on the same rows.
Check that the formula has a closing parenthesis and commas between arguments.

For a small, controlled test, replace criteria-cell references with known values and verify the matching records manually. If that works, the issue is likely in a referenced cell, rather than in SUMIFS itself.

Criteria that do not match the data

SUMIFS compares criteria with cell contents, not with what you assume those contents mean. Extra spaces, inconsistent labels, punctuation, and spelling differences can prevent a match. A cell containing “North ” with a trailing space is not necessarily equivalent to one containing “North”. Text criteria are generally not case-sensitive, so capitalization alone usually does not explain a missing match, but spaces and other characters can.

You can use wildcards for partial text matches. An asterisk represents any sequence of characters, while a question mark represents one character. A tilde escapes a wildcard when you need to search for the literal character. With region labels in A2:A5 and amounts in B2:B5, where the amounts are 120, 150, 60, and 90 for North-East, North-West, North, and South respectively, this formula includes all labels beginning with North:

=SUMIFS(B2:B5,A2:A5,"North*")

It returns 330 because the first three amounts total 120 + 150 + 60. Wildcards are useful when labels have suffixes, but they can also make a criterion too broad. For instance, North* can match North-West as well as North, so inspect the underlying labels before relying on a partial match.

For numeric comparisons, put the operator and value together in the criterion. With values in B2:B5 of 120, 80, 150, and 60, and a threshold of 100 in E2, this formula adds values strictly greater than the threshold:

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

It returns 270 from 120 and 150. Use >= if the threshold itself should qualify. When typing a criterion directly, comparison operators belong inside quotation marks; when the threshold comes from a cell, join the operator to the cell reference with the ampersand.

Dates, numbers, and text-stored values

Dates in Excel are stored as serial numbers, although cells commonly display them in a date format. A criterion can fail if a date-looking entry is actually text, or if the formula compares a full date and time against a value that includes a time component. Check the cell’s underlying value and formatting, and use real Excel dates consistently in the source data.

For a month total, a start-inclusive and next-month-exclusive boundary is often more dependable than trying to construct the last moment of the month. Suppose D2:D5 contains January 3, January 20, February 1, and January 31, 2026, while B2:B5 contains 100, 150, 90, and 50. This formula sums January records:

=SUMIFS(B2:B5,D2:D5,">="&DATE(2026,1,1),D2:D5,"<"&DATE(2026,2,1))

The result is 300: 100 + 150 + 50. The February 1 record is excluded. The exclusive upper boundary also includes January entries that contain a time, such as January 31 at 3:00 p.m., because those values are still earlier than February 1.

Another common issue is numbers stored as text. A cell may look like 120 but be stored as text because it was imported or entered with an unexpected apostrophe or separator. SUMIFS does not reliably treat text-formatted numeric data as the numeric values you intended to total. Check a few source cells, convert the underlying data to numbers where appropriate, and then recalculate. Changing the display format alone does not convert text into a number.

Blanks, operators, and cell references

Criteria can be typed into the formula or stored in worksheet cells. Referencing cells makes a summary easier to update, but the referenced value still has to match the data and any comparison operator must be constructed correctly. With regions in A2:A6, amounts in B2:B6, statuses in C2:C6, North in E2, and Paid in F2, this formula uses the two criteria cells:

=SUMIFS(B2:B6,A2:A6,E2,C2:C6,F2)

It returns 270 for the example data above. If E2 or F2 contains an unintended space, or points to the wrong cell, the formula can return zero even though the visible labels appear correct.

Blank criteria deserve special attention. A criterion of "" is used to match empty cells, while "<>" can be used to match nonblank cells. A formula that refers to an empty criteria cell may behave differently from what you intended, especially if you meant to omit that condition. Check the criterion cell itself and decide whether blank records should be included, excluded, or treated as a separate category.

Diagnose zero results and Excel errors

A zero is not always an error: it can be a legitimate total, or it can mean that no rows matched all the criteria. Start by checking each condition separately. Filter the source table or inspect the relevant columns to see whether at least one row meets every condition. If each condition finds matches alone but the combined formula returns zero, the conditions may be mutually exclusive or attached to the wrong ranges.

Other common symptoms give useful clues:

#VALUE! Check that the ranges have matching dimensions and that the formula’s arguments are valid.
The formula displays as text. The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then re-enter the formula.
The displayed answer does not update. Check Formulas > Calculation Options and confirm calculation is set to Automatic. Press F9 to recalculate the workbook if needed.
The result is too large or too small. Look for overlapping criteria, duplicated source rows, a sum range in the wrong column, or a wildcard that matches more labels than intended.
Excel shows a formula error while typing. Check separators, quotation marks, parentheses, and whether the criteria contain a valid operator or reference.

On Windows, F9 recalculates formulas; on a Mac, the recalculation shortcut can depend on the keyboard and Excel settings, so use Formulas > Calculate Now if the key does not work. Recalculation will not fix a bad match or incorrect range, but it can distinguish a stale displayed result from a formula-logic problem.

A practical troubleshooting sequence

Work from the simplest possible case toward the full formula. This is faster than changing several criteria at once and losing track of what caused the result to change.

Confirm that the formula begins with an equals sign and that the first argument is the range to sum.
Temporarily test one criterion, then add the other criteria back one pair at a time.
Check the source cells for hidden spaces, inconsistent punctuation, text-stored numbers, and text-stored dates.
Compare every range’s start and end rows, and verify that the formula points to the intended worksheet and columns.
Check the result against a small manual calculation or a filtered view of the matching rows.
Restore the full formula and, if the criteria will change regularly, use cell references rather than repeatedly editing quoted values.

If the formula uses whole-column references, verify that the selected columns are correct and that the workbook remains responsive. Bounded ranges or an Excel Table can make a summary easier to maintain as records are added. Tables expand with new rows, but formula references must still point to the intended table columns and criteria.

SUMIFS compared with related options

Choose SUMIFS when you need to total numeric values subject to several conditions. Use SUMIF when there is just one condition; it can be simpler to read, but its argument order differs. For counting qualifying rows rather than adding amounts, COUNTIFS is the related function. If the task is to total every row in a filtered list, a filter or PivotTable may be easier to audit than a long formula, especially for a report that needs to be reviewed by other people.

SUMIFS is not a general-purpose test for every kind of logic. Its separate criteria pairs use AND logic, not “this condition or that condition.” For an OR total, options include adding separate SUMIFS results when the categories do not overlap, or restructuring the data and summary. If categories overlap, adding separate results can double-count rows, so first decide what the intended total represents.

Frequently Asked Questions

Why does SUMIFS return 0 when matching values are visible?
Check for extra spaces, text-versus-number or text-versus-date differences, a criteria cell that points to the wrong place, and criteria ranges that do not align with the intended rows. Also confirm that all conditions can be true on the same record.

Does the order of SUMIFS criteria matter?
The criteria pairs can generally be rearranged as long as each criterion stays paired with its correct criteria range. The sum range must remain first, and every pair must refer to the intended data.

Can SUMIFS use a date range?
Yes. Use a lower bound and an upper bound as separate criteria on the date column. For monthly totals, an inclusive first day and an exclusive first day of the next month also handles dates that include times.

Why does SUMIFS return #VALUE!?
Check for sum and criteria ranges with different dimensions, a malformed argument, or a reference to an invalid range. Make sure each range covers corresponding records and the formula’s parentheses and separators are balanced.

Is SUMIFS case-sensitive?
No. Standard SUMIFS text matching is not case-sensitive. If uppercase and lowercase labels need to count as different categories, SUMIFS alone is not the right case-sensitive matching method; the data or calculation approach must be adjusted.

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.