SUMIF Function

Adds numbers within a range that meet a specified condition
Sample Usage
SUMIF(Quantity:Quantity, >25, Cost:Cost)
Syntax
SUMIF(
  • range
  • criterion
  • [
    sum_range
    ]
)
  • range
    The group of cells to evaluate
  • criterion
    The condition that defines which numbers to add (for example: 15, "Hello World!", >25)
  • sum_range
    —[optional]
    The group of cells to add, if different from the range
Usage Notes
  • For criterion, acceptable operators include: = (equal to), <> (not equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to)
Examples

This example references the following sheet information:

  Clothing Item Transaction Total Units Sold Price Per Unit Sold Date
1 T-Shirt 1,170.00 78 15.00 02/12/19
2 Pants 1,491.00 42 35.50 02/15/19
3 Jacket 812.00 217 200.00 03/20/19

Given the table above, here are some examples of using SUMIF in a sheet:

Formula Description Result
=SUMIF([Price Per Unit]1:[Price Per Unit]3, >20, [Transaction Total]1:[Transaction Total]3) Revenue Gained. Sum the Transaction Total if the price per unit is greater than 20.00 2,303.00
=SUMIF([Clothing Item]1:[Clothing Item]3, "Pants", [Units Sold]1:[Units Sold]3) Sums units sold based on the item. In this example, the function sums units sold, if the value in the Clothing Item column equals Pants 42
=SUMIF([Sold Date]1:[Sold Date]3, MONTH(@cell) = 2, [Transaction Total]1:[Transaction Total]3) Revenue Gained. Sums the Transaction Total column, if the month in the Sold Date column is 2 (February).  2,661.00

Still need help?

Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community