What Does The Sumif Function Do?

The SUMIF function in spreadsheet applications like Microsoft Excel, Google Sheets, and LibreOffice Calc is used to sum the values in a range of cells that meet a single specified criterion. This allows you to perform conditional summing, adding up numbers only when a certain condition is met in a corresponding range of cells.

Syntax and arguments

The SUMIF function has the following syntax:=SUMIF(range, criteria, [sum_range])

• range (Required): The range of cells that you want to evaluate with your specified criteria. These are the cells that will be checked to see if they meet the condition.
• criteria (Required): The condition that determines which cells in the range will trigger a summation. The criteria can be a number, expression, a cell reference, or text. Text and logical operators must be enclosed in double quotation marks ("").
• sum_range (Optional): The actual range of cells to be summed. If this argument is omitted, the cells in the range argument are summed instead. It is important for the range and sum_range to have the same number of rows and columns to prevent calculation errors.

Real-world examples

The power of SUMIF is best understood through practical applications. Imagine a sales data table with columns for Region, Product, and Sales Amount.

Here are some conditional summing tasks you could perform using SUMIF:

Summing sales for a specific text value

To find the total sales for the "Apples" product:=SUMIF(B2:B6, "Apples", C2:C6)

• range: B2:B6 (the column with product names)
• criteria: "Apples"
• sum_range: C2:C6 (the sales amounts)
• Result: $2,950 (from $800 + $1,200 + $950).

Summing based on a numeric condition

To sum sales amounts over a specific value, like $1,000:=SUMIF(C2:C6, ">1000")

• range: C2:C6 (the sales amounts column)
• criteria: ">1000"
• sum_range: The optional argument is omitted, so the range is summed.
• Result: $2,700 (from $1,200 + $1,500).

Using wildcards for partial text matches

If you want to sum sales for all products in the "North" region, which also includes "North-East," you can use a wildcard (*).=SUMIF(A2:A6, "North*", C2:C6)

• range: A2:A6 (the region column)
• criteria: "North*" (matches any text starting with "North")
• sum_range: C2:C6 (the sales amounts)
• Result: $2,200 (from $1,000 + $1,200).

Referencing a cell for criteria

For more flexible formulas, you can reference a cell that contains your criteria. If cell E1 contains the text "Apples", you can write:=SUMIF(B2:B6, E1, C2:C6)This allows you to change the product name in cell E1 and the formula will update automatically.

Important considerations

• Case-insensitivity:SUMIF is not case-sensitive, so "Apples" and "apples" are treated as the same criteria.
• One condition only:SUMIF is limited to a single condition. If you need to sum based on multiple criteria (e.g., sales in the "North" region and for the "Apples" product), you must use the SUMIFS function.
• Syntax difference with SUMIFS: Be careful when moving between SUMIF and SUMIFS, as the order of the sum_range and criteria_range arguments is different. In SUMIF, the sum_range is the final argument, while in SUMIFS, it is the first argument.
• Filtered data: A standard SUMIF function will sum values from all rows, even those that are hidden by a filter. To sum only the visible rows in a filtered table, you must use a more advanced function like AGGREGATE or a combination of SUM and SUBTOTAL.

Comparison with related functions

• SUMIFS: The more powerful and recommended version for modern spreadsheets, as it handles multiple criteria simultaneously. Its syntax is =SUMIFS(sum_range, criteria_range1, criteria1, ...).
• COUNTIF: Counts the number of cells within a range that meet a single, specified criterion. It is the "counting" equivalent of SUMIF.
• SUM: Adds all the numbers in a range of cells, without any conditions. It's the simplest summation function.