SUMIF function

SUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.

=SUMIF(range, criteria, [sum_range])

Generate a SUMIF formula

Describe what you need. The generator will reach for SUMIF where SUMIF is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How SUMIF reads its arguments
rangerequiredcriteriarequiredsum_rangeoptionalSUMIF →
ArgumentRequiredDescription
rangeRequiredRequired. The range of cells to evaluate against the criteria. Can contain text, numbers, dates, or mixed types. Each cell is tested individually against the criteria condition.
criteriaRequiredRequired. The condition that determines which cells qualify. Can be a text string ("Alice"), number (5), date, or comparison operator (like ">4" or "<>North"). Use wildcards: * matches any sequence of characters, ? matches single characters.
sum_rangeOptionalOptional. The range of cells to sum. If omitted, the range itself is summed. Must have the same row count as range, though columns can differ. Non-numeric values are automatically ignored during summation.

Returns

A number representing the sum total of matched cells.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Sum a sales rep's total units

Order IDRegionRepUnitsUnit PriceDateRevenue
1001NorthAlice512.501/15/202462.50
1002SouthBob325.001/16/202475.00
1003NorthAlice815.751/17/2024126.00
1004EastCharlie230.001/18/202460.00
1005WestDavid620.001/19/2024120.00
1006SouthBob422.501/20/202490.00
1007NorthAlice718.001/21/2024126.00
=SUMIF(C:C,"Alice",D:D)

Result: 20

The formula searches column C (Rep) for "Alice", finding her in three rows with units of 5, 8, and 7. It sums the corresponding values from column D (Units), yielding 5 + 8 + 7 = 20 total units sold by Alice.

2. Sum revenue by sales region

Order IDRegionRepUnitsUnit PriceDateRevenue
1001NorthAlice512.501/15/202462.50
1002SouthBob325.001/16/202475.00
1003NorthAlice815.751/17/2024126.00
1004EastCharlie230.001/18/202460.00
1005WestDavid620.001/19/2024120.00
1006SouthBob422.501/20/202490.00
1007NorthAlice718.001/21/2024126.00
=SUMIF(B:B,"North",G:G)

Result: 314.5

The formula checks column B (Region) for "North", matching rows 1, 3, and 7. It then sums the corresponding revenues from column G: 62.50 + 126.00 + 126.00 = $314.50 total revenue from the North region.

3. Sum units for orders exceeding a threshold

Order IDRegionRepUnitsUnit PriceDateRevenue
1001NorthAlice512.501/15/202462.50
1002SouthBob325.001/16/202475.00
1003NorthAlice815.751/17/2024126.00
1004EastCharlie230.001/18/202460.00
1005WestDavid620.001/19/2024120.00
1006SouthBob422.501/20/202490.00
1007NorthAlice718.001/21/2024126.00
=SUMIF(D:D,">4",D:D)

Result: 26

The formula uses ">4" as the criteria to filter column D (Units). Orders with more than 4 units are rows with 5, 8, 6, and 7 units. These are summed: 5 + 8 + 6 + 7 = 26 units across large orders.

Common errors

Which SUMIF error are you seeing?
SUMIF returned an error#VALUE!
Review the criteria string for paired quotation marks. For comparison operators, use valid syntax only: >, <, >=, <=, <>, or =. Example: change "=>5" to ">5".
#REF!
Restore any deleted rows or columns, or update the range references to valid cell addresses that currently exist. Verify named ranges are still defined if used.
#NULL!
Ensure both range and sum_range contain the same number of rows. They can span different columns, but the row counts must match exactly.
ErrorWhy it happensHow to fix it
#VALUE!The criteria argument contains invalid syntax, such as mismatched quotation marks (opening " without closing, or vice versa) or malformed comparison operators like "=>5" or "<=<North".Review the criteria string for paired quotation marks. For comparison operators, use valid syntax only: >, <, >=, <=, <>, or =. Example: change "=>5" to ">5".
#REF!Occurs when range or sum_range references cells in rows or columns that have been deleted from the worksheet, or when a named range used in the formula has been removed.Restore any deleted rows or columns, or update the range references to valid cell addresses that currently exist. Verify named ranges are still defined if used.
#NULL!The range and sum_range arguments have different numbers of rows, causing a mismatch when attempting to align criteria matches against the cells to sum.Ensure both range and sum_range contain the same number of rows. They can span different columns, but the row counts must match exactly.

Tips and when to use something else

  • Use SUMIFS (note the plural) when you need to sum based on multiple criteria across different columns.
  • Criteria support wildcards: use "*North*" to match any cell containing 'North', or "A?ice" to match 'Alice', 'Atice', etc.
  • If sum_range is omitted, SUMIF sums the range itself—useful for quick totals when the criteria column is also the column to sum.
  • For complex calculations like (Units × Price) or multi-column sums, use SUMPRODUCT instead; SUMIF only sums existing range values, not computed results.

Related functions

SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.

Frequently asked questions

How do I sum data based on multiple criteria?
Use SUMIFS instead. For example, =SUMIFS(G:G, B:B, "North", C:C, "Alice") sums revenue (column G) where region is North AND rep is Alice. SUMIF only handles one criterion at a time.
Can I sum based on a date range?
Yes. Use date comparison criteria like =SUMIF(F:F, ">="&DATE(2024,1,15), G:G) to sum values where dates are after 1/15/2024. You can combine >= and <= criteria to create a date range filter.
What happens if no cells match the criteria?
SUMIF returns 0. This is the sum of an empty set. No error is raised, which allows you to safely use SUMIF for counts of unmatched data without needing error handling.
Is SUMIF case-sensitive when matching text?
No, SUMIF is case-insensitive by default. Criteria "alice", "ALICE", and "Alice" all match the same cells. If you need case-sensitive matching, use SUMPRODUCT with EXACT instead.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17