SUMIFS Explained: Add Cells That Meet Several Conditions

The sum range comes first, then pairs of range and condition

The Excel syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). Only sum_range, criteria_range1 and criteria1 are required. Microsoft lists it for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, Excel 2016 and the Excel Web App.

Each criteria range has to be the same number of rows and columns as sum_range. A row is added only if it passes all the tests, so every extra pair narrows the result. Google Sheets uses the same layout and says the row must meet all criteria in a row to be summed.

SUMIF puts the sum range third, SUMIFS puts it first

Microsoft warns that the argument order differs. In SUMIF(range, criteria, [sum_range]) the sum_range is third and optional. In SUMIFS it is first and required. Copying a SUMIF formula and adding a second condition without moving the sum range is a classic way to get a wrong total.

SUMIF also tolerates a sum_range of a different size: Excel uses a block of the range's size, starting at the first sum_range cell. SUMIFS does not tolerate it, so a mismatch in row counts is something to fix, not to ignore.

A worked example with Microsoft's 8-row sales table

Put Quantity Sold in A2:A9, Product in B2:B9 and Salesperson in C2:C9. The rows are: 5 Apples Tom, 4 Apples Sarah, 15 Artichokes Tom, 3 Artichokes Sarah, 22 Bananas Tom, 12 Bananas Sarah, 10 Carrots Tom, 33 Carrots Sarah.

Question 1: how many items starting with A did Tom sell? =SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom") returns 20, from 5 Apples and 15 Artichokes. Question 2: everything Tom sold except bananas? =SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, "Tom") returns 30, from 5, 15 and 10.

Check by hand: Tom's 4 rows add to 5 + 15 + 22 + 10 = 52. Removing his 22 bananas leaves 30, which matches the formula.

Change "Tom" to "Sarah" in the same formula and the first question returns 4 + 3 = 7, since Sarah sold 4 Apples and 3 Artichokes. Her 4 rows total 4 + 3 + 12 + 33 = 52, the same as Tom's by coincidence. Checking one answer by hand on 8 rows takes a minute and tells you whether the criteria mean what you think.

What the criteria can look like

Criteria accepts a number such as 32, an expression such as ">32", a cell reference such as B4, or text such as "apples". Put the comparison and the value in one quoted string, or join a cell with an ampersand, as in ">"&B4.

• > and < with a number: ">20" adds rows where the criteria range is above 20.
• <> means not equal, as in "<>Bananas".
• ? matches exactly one character, so "To?" matches Tom.
• * matches any run of characters, so "=A*" matches Apples and Artichokes.
• ~ before ? or * searches for that literal character.

Why SUMIFS returns 0, a wrong total or an error

Microsoft says text criteria such as a name must be in quotes, otherwise the result can be 0. In sum_range, TRUE counts as 1 and FALSE as 0, so a stray logical value changes the total.

Spaces matter too. The COUNTIF page warns that leading spaces, trailing spaces and nonprinting characters give unexpected results, and TRIM and CLEAN are the cleaners it names. A product typed "Apples " with a trailing space will not match "Apples".

The older SUMIF page adds that matching strings longer than 255 characters returns incorrect results. Keep text conditions short and put long keys in a helper column.

Sizing a long SUMIFS: 127 pairs, 255 arguments, 8,192 characters

Excel caps any function at 255 arguments, and a SUMIFS with 127 pairs plus the sum range uses exactly 255, so the pair limit and the argument limit meet. A formula can also hold at most 8,192 characters. In practice a long text condition or a sheet name repeated in every range will hit the character limit before the pair limit.

Two habits keep long formulas short. Point each condition at a cell, as in ">"&E1, so the value is changed in one place. Use whole, equal-height ranges such as A2:A9 and C2:C9 for every pair, so the sizes cannot drift apart when you copy the formula down 10 rows or 100.

Google Sheets: same order, one gap

Google documents SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]). Its examples include =SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<30") and =SUMIFS(C1:C100, E1:E100, "Yes"). Summing across multiple columns is not supported.

The Google SUMIFS page does not cover wildcards, so test patterns with * in a small range before using them in Sheets.

Who SUMIFS suits, and when to use something else

It suits totals by 2 or 3 conditions in a flat table: sales by product and person, hours by project and month. For one condition, SUMIF is enough. For OR logic across a single column, add 2 SUMIFS together, because SUMIFS itself needs every pair to be true.

If a total looks wrong and you are looking at the sheet right now, Crade can read the screen and check whether the ranges line up.