SUMIFS With Three Criteria Not Working? Fix It

If SUMIFS with three criteria is not working, check that the formula has one sum range followed by three correctly paired criteria-range and criteria arguments, and that all four ranges are the same size. In Excel for Microsoft 365 on Windows, a typical three-condition formula is =SUMIFS($D$2:$D$7,$A$2:$A$7,F2,$B$2:$B$7,F3,$C$2:$C$7,F4); the sections below show how to diagnose the result when it is wrong or zero.

How SUMIFS applies three criteria

SUMIFS adds values from a sum range only for rows that meet every supplied condition. Each condition consists of a criteria range and a criterion, entered as a pair. For three criteria, the function therefore needs seven arguments in this order: sum range, first criteria range, first criterion, second criteria range, second criterion, third criteria range, third criterion.

The conditions are combined as AND, not OR. If the criteria are Region = North, Rep = Lee, and Status = Paid, a row contributes only when all three statements are true for that same row. A row that matches two conditions but not the third is excluded. This is the most important point to check when a formula appears to be overlooking otherwise relevant records.

SUMIFS is available in current Excel, including Microsoft 365, and the same basic syntax works in Excel for the web and Excel for Mac. The displayed formulas use commas, as in English-language Excel installations. Some regional settings use semicolons between arguments; if Excel rejects a formula with commas, check the list separator used by your installation rather than changing the criteria logic.

Check the argument order and range sizes

The sum range comes first. Each criteria range must then be followed immediately by the value or expression that should be tested against it. A common mistake is to put all three ranges together and then all three criteria; SUMIFS requires alternating range-and-criterion pairs.

Use this quick checklist before changing the data:

Count one sum range and three complete criteria pairs. A missing criterion or an extra comma can shift the arguments.
Confirm that the sum range and every criteria range cover the same number of rows and columns. For example, D2:D100 should pair with A2:A100, not A2:A99.
Make sure each range points to the intended field. A correct criterion applied to the wrong column will usually return zero or an unexpected total.
When copying the formula, lock ranges with dollar signs if they should not move, while leaving a criterion-cell reference relative if it should change by row.

SUMIFS can accept more than three criteria pairs, but adding pairs does not change the AND behavior: every pair must match for a row to be included. For a troubleshooting task, begin with exactly the three conditions you need and verify each one independently.

Work through a concrete example

Suppose a sales list has Region in A2:A7, Rep in B2:B7, Status in C2:C7, and Amount in D2:D7. The six records are North/Lee/Paid/120, North/Lee/Pending/80, North/Park/Paid/200, South/Lee/Paid/150, North/Lee/Paid/90, and North/Lee/Paid/60. Put North in F2, Lee in F3, and Paid in F4.

With those records in A2:D7 and the criteria in F2:F4, this formula returns 270: =SUMIFS($D$2:$D$7,$A$2:$A$7,F2,$B$2:$B$7,F3,$C$2:$C$7,F4). The matching amounts are 120, 90, and 60. The pending 80 is rejected by the status condition, while the Park and South records fail another condition.

This small example is useful because each criterion has a separate column and each row is a complete record. If your source data instead stores several details in one cell, or the fields are on different rows, SUMIFS may not be the right structure until the data is normalized. Each criteria range is evaluated row by row against the corresponding position in the other ranges.

Diagnose a zero or unexpectedly low total

A result of zero does not necessarily mean the function is broken. It often means no row satisfies all three conditions exactly as Excel reads them. Temporarily test fewer conditions to find the one that excludes the records you expect. With the sales example above, the first two criteria identify four North/Lee rows, and adding Paid reduces the matches to three.

For a systematic check, inspect the source rows that you expect to count and compare each cell with its criterion. Watch for leading or trailing spaces, different spellings, hidden characters, and inconsistent labels such as Paid, paid, and Paid with an added space. Excel’s text matching in SUMIFS is generally not case-sensitive, but extra spaces and different characters still matter.

Also verify that the amount cells contain numbers, not numeric-looking text. A value imported as text may not be added as intended. Select a sample amount and inspect its alignment or use Excel’s number conversion tools; simply applying a currency format changes appearance, not the underlying value. If the result is too high, check for duplicate source rows and whether the criteria are broad enough to include additional records.

Check text, numbers, dates, and wildcards

Text criteria should match the data values, allowing for the intended spelling and spacing. A criterion cell is often safer than typing a label repeatedly because it is easy to inspect and update. Numeric comparisons such as greater than or less than need an operator joined to a value. For example, a criterion of greater than 100 is entered as the text criterion ">100"; a reference-based threshold is joined with the operator using concatenation.

For date criteria, use real Excel dates rather than text that only looks like a date. If the cutoff date is in G2 and the dates are in C2:C7, this layout uses amount in D2:D7, region in A2:A7, rep in B2:B7, and dates in C2:C7. To sum North/Lee amounts through and including the date in G2, use =SUMIFS($D$2:$D$7,$A$2:$A$7,"North",$B$2:$B$7,"Lee",$C$2:$C$7,"<="&G2). The result depends on the dates and amounts in those cells. The ampersand joins the comparison operator to the date value; without it, the date condition may not be interpreted as intended.

SUMIFS also supports wildcards in text criteria: an asterisk matches any number of characters, and a question mark matches one character. For example, a criterion such as "Lee*" can match a text value beginning with Lee. If you need to match an actual asterisk or question mark in the data, prefix that character with a tilde. Use wildcards only when a partial match is intended; they can silently include more records than an exact criterion.

Understand AND versus OR conditions

Three ordinary SUMIFS criteria mean that all three must be true for each included row. That is suitable for Region = North AND Rep = Lee AND Status = Paid. A frequent misconception is that listing two possible statuses or regions as extra criteria will make Excel accept either one. Instead, separate criteria pairs normally add another AND requirement.

For an OR condition, such as adding North and South sales for the same Lee/Paid criteria, add the results of two SUMIFS calculations. With Region in A2:A7, Rep in B2:B7, Status in C2:C7, and Amount in D2:D7 as in the example, the formula below returns 420: the North/Lee/Paid records total 270 and the South/Lee/Paid record adds 150.

For those same ranges and records, use =SUM(SUMIFS($D$2:$D$7,$A$2:$A$7,"North",$B$2:$B$7,"Lee",$C$2:$C$7,"Paid"),SUMIFS($D$2:$D$7,$A$2:$A$7,"South",$B$2:$B$7,"Lee",$C$2:$C$7,"Paid")). This approach is clear when there are only a few alternatives. If the OR list is long or maintained in a worksheet, a helper column, PivotTable, or a more structured formula may be easier to audit.

Use Excel’s formula tools to find the fault

Check the formula in the formula bar and click through its references to see which cells Excel highlights. On Windows, use Formulas > Evaluate Formula to step through how Excel evaluates a formula; the path may be slightly different in other Excel versions. In Excel for Mac, the Formulas tab also includes formula-auditing tools, though ribbon layout can differ. Excel for the web supports SUMIFS, but some desktop formula-auditing features may not be available there.

It can also help to copy one criterion range and its paired criterion into a temporary SUMIFS test, then add the other pairs back one at a time. Keep the sum range fixed and ensure each test range still covers the same rows. This isolates a mismatch without editing the underlying data. If the formula returns an error rather than zero, inspect for malformed references, missing quotation marks around typed text criteria, or mismatched parentheses.

When formulas are copied down, inspect whether the criteria references and source ranges moved as intended. Dollar signs make a reference absolute: they prevent the referenced row or column from shifting when copied. A relative reference to F2 can change to F3 on the next row, which may be exactly what a summary table needs. Do not lock every reference automatically; decide which cells should remain fixed and which should follow the formula.

When to use a different approach

SUMIFS is usually the simplest choice when the data is in consistent columns and the conditions are straightforward. A PivotTable can be more convenient for exploring totals by several fields, especially when the desired grouping changes often. A helper column can make complicated row-level logic visible and easier for a team to review. These alternatives do not fix dirty source data, so check data consistency regardless of the method.

If the conditions involve complex combinations of AND and OR, or transformations of each row before summing, a single SUMIFS may not express the logic cleanly. Break the logic into several clearly labeled totals or use a suitable formula approach, then verify it against a small hand-calculated sample. Avoid making the formula more complex than the data problem requires; transparent criteria are easier to maintain than a compact but opaque construction.

Frequently Asked Questions

Why does SUMIFS return zero when all three values seem to match?
At least one criterion may not match exactly, or the ranges may be misaligned. Check spaces, date and number types, spelling, and whether all ranges cover the same rows. Test each condition separately to identify the one excluding the record.

Do three SUMIFS criteria mean AND or OR?
They mean AND: every criteria pair must be true on the same row for its amount to be included. For OR alternatives, add separate SUMIFS results or choose another method suited to the logic.

Can I use cell references for all three criteria?
Yes. Put each criterion in a cell and use that cell reference as the paired criterion argument. This makes it easier to change the conditions and inspect what the formula is testing.

Why does SUMIFS show an error after I add a third condition?
Check that the arguments still alternate between criteria range and criterion, and that commas or your regional list separators are correct. Also confirm that each range is valid and covers the same dimensions as the sum range.

Can SUMIFS criteria use dates and comparison operators?
Yes. Use genuine Excel date values and construct a comparison criterion by joining an operator such as less than or equal to with a date cell. This avoids relying on ambiguous date text.

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.