Google Sheets SUMIF: Syntax, Examples, and Common Fixes

To add values in Google Sheets only when matching cells meet a condition, use SUMIF. For example, if A2:A6 contains item names and B2:B6 contains amounts, =SUMIF(A2:A6,"Apple",B2:B6) adds the amounts beside Apple; with Apple amounts of 12, 8, and 15, the result is 35.

What SUMIF does and how it works

SUMIF checks cells against one condition, then adds values from the corresponding cells in a sum range. In the example above, Sheets checks each cell in A2:A6 for the text Apple and adds the value in the same row of B2:B6 whenever it finds a match. The criterion range and the sum range therefore need to line up row by row.

The function has three arguments: the range to check, the criterion to apply, and the range to add. The third argument is optional. If you leave it out, Sheets checks and adds values in the same range. That is useful when you want to total numbers that themselves meet a condition, such as values greater than 100.

Enter a formula in a blank cell, starting with an equals sign, and press Enter. Google Sheets calculates the result and updates it when relevant source cells change. The examples here use comma separators, as is common in many locales. Depending on your spreadsheet’s locale, Sheets may require semicolons between arguments instead.

Build a basic SUMIF formula

Start by identifying two ranges: the cells that contain the labels or values to test, and the cells that contain the numbers to total. Keep the rows aligned. In the opening example, A2:A6 is the criterion range and B2:B6 is the sum range. The formula returns 35 because the three Apple rows contain 12, 8, and 15.

If the numbers to test are also the numbers you want to add, the sum range can be omitted. With quantities in B2:B6, this formula totals only quantities above 100:

Assume B2:B6 contains 80, 125, 160, 95, and 110. =SUMIF(B2:B6,">100") returns 395, because 125 + 160 + 110 = 395.

When a sum range is supplied, it does not have to contain the same kind of information as the criterion range. For instance, the first range can hold department names while the second holds expenses. Use ranges of compatible size, and check that each row in one range corresponds to the intended row in the other.

Use text, numbers, and wildcards as criteria

A criterion can be an exact text value, a number, or a comparison such as greater than or less than. Text criteria are written in quotation marks in the formula. Comparison operators such as >, >=, <, and <= are usually included in a quoted criterion. For example, with test values in B2:B5 and amounts in C2:C5, =SUMIF(B2:B5,">100",C2:C5) adds the amounts for rows whose B value exceeds 100.

Assume B2:B5 contains 80, 125, 160, and 95, while C2:C5 contains 40, 70, 55, and 30. That formula returns 125: only the amounts 70 and 55 meet the condition, and 70 + 55 = 125. Notice that the formula tests column B but adds column C.

To compare against a value stored in another cell, join the operator to the cell reference with an ampersand. If E2 contains 100 and B2:B5 and C2:C5 contain the values just described, =SUMIF(B2:B5,">"&E2,C2:C5) also returns 125. This makes the threshold easy to change without editing the formula.

For text that only partly matches, use wildcards. An asterisk (*) matches any number of characters, while a question mark (?) matches one character. The tilde (~) lets you search for a literal wildcard character. For example, if A2:A4 contains North-East, South-East, and North-West and B2:B4 contains 30, 25, and 40, =SUMIF(A2:A4,"*-East",B2:B4) returns 55. It matches both labels ending in -East. Text matching with SUMIF is not case-sensitive, so Apple and APPLE are treated as the same text.

Apply more than one condition with SUMIFS

SUMIF handles one condition. When the total must satisfy multiple conditions at once—for example, a region and a payment status—use SUMIFS. Its argument order differs: put the sum range first, followed by each criterion range and its condition. Every condition must be true for a row to be included.

Assume B2:B5 contains East, West, East, and East; C2:C5 contains Paid, Paid, Pending, and Paid; and D2:D5 contains 50, 40, 30, and 70. =SUMIFS(D2:D5,B2:B5,"East",C2:C5,"Paid") returns 120, adding 50 and 70. The East row marked Pending is excluded, as is the West row marked Paid.

Use SUMIF when one test is enough; use SUMIFS when several tests must all match. You can also add several SUMIF formulas when you specifically need separate totals for different conditions, but SUMIFS is usually easier to read for combined conditions.

Sum values within a date range

Dates are stored as values in spreadsheets, so date conditions can use comparison operators. To total a whole year, use a start date inclusive and the start of the following year exclusive. This approach includes every date in the selected year, including dates with times, without needing to invent a final moment on December 31.

Assume A2:A6 contains dates with corresponding sales in B2:B6: December 20, 2025 (10), January 3, 2026 (20), June 8, 2026 (35), December 29, 2026 (45), and January 5, 2027 (15). =SUMIFS(B2:B6,A2:A6,">="&DATE(2026,1,1),A2:A6,"<"&DATE(2027,1,1)) returns 100, because 20 + 35 + 45 = 100.

The DATE function constructs a date from year, month, and day, avoiding ambiguity between regional date formats. If you keep the start and end dates in cells instead, you can build criteria from those references in the same way as the threshold example. Ensure the cells contain real date values rather than text that merely looks like a date.

Common SUMIF problems and how to fix them

The result is zero: Check that the criterion actually matches the cells. Extra spaces, misspellings, numbers stored as text, or a criterion with the wrong comparison operator can prevent matches. If you are comparing against a cell, confirm that the reference points to the intended value.
The total is too high or too low: Verify that the sum range is aligned with the criterion range. If one range starts on row 2 and the other on row 3, the function may add values from the wrong records. Also check for repeated source rows that may legitimately be counted more than once.
A comparison criterion does not work: Put the operator and fixed value together in quotes, as in ">100". When the value comes from a cell, join the quoted operator and reference with &. A bare comparison operator is not the same as a complete criterion.
A date condition misses expected rows: Confirm that the source cells are dates and not text. Use a clear start-inclusive, end-exclusive range for periods, and check that the boundary dates correspond to the intended year or month.
The formula shows an error: Check that parentheses are balanced, ranges are valid, and the argument separators match your spreadsheet locale. Also make sure quotation marks around text criteria are straight quotes, not typographic curly quotes copied from formatted text.

When debugging, first test the criterion range and the sum range separately by inspecting a few matching rows. A simple, clearly labeled sheet also helps: keep headers outside the ranges, use consistent data types in each column, and avoid including totals or notes within the source data unless you intend them to be evaluated.

SUMIF in Google Sheets compared with Excel

SUMIF is available in both Google Sheets and Microsoft Excel, and the basic argument order and examples here are generally familiar to Excel users. The main differences a reader is likely to notice are in the interface and spreadsheet settings, not the core purpose of the function. This guide is for Google Sheets in a web browser; enter the formula directly in a cell or the formula bar.

Formula separators can depend on locale in either spreadsheet application, so a comma-separated example may need semicolons in a sheet configured for a locale that uses them. Spreadsheet functions can also behave differently when data is imported or formatted as text, so check the actual cell values if a result differs between applications. Do not assume that a displayed format, such as a currency symbol or date pattern, changes the underlying value SUMIF evaluates.

Frequently Asked Questions

How do I sum one column based on text in another column?
Put the text column in SUMIF’s first range, the exact text as the criterion, and the numbers column as the sum range. For example, the opening formula adds B values for rows where A contains Apple.

Can SUMIF use a cell reference as its condition?
Yes. For a comparison such as greater than a threshold in E2, join the quoted operator to the reference with an ampersand. For an exact match, you can use the criterion cell reference directly.

Can SUMIF add values that contain a word?
Yes. Use an asterisk on either side of the word in a text criterion to match text that contains it, such as a label with extra characters before or after. Remember that the asterisk is a wildcard, not a literal character unless escaped with a tilde.

Why does SUMIF return zero even though the text looks identical?
Check for hidden spaces, inconsistent text, or mismatched data types. Text matching ignores capitalization, but it does not make unrelated labels or values equivalent. Also check whether the formula is testing the intended range.

What is the difference between SUMIF and SUMIFS?
SUMIF applies one condition and puts the criterion range first. SUMIFS can apply multiple conditions and puts the sum range first. Choose SUMIFS when a row must meet several tests before its value is added.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.