Adds numbers within a range that meet a specified condition
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
Sample usage
SUMIF(Quantity:Quantity, >25, Cost:Cost)
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/23 |
| 2 | Pants | 1,491.00 | 42 | 35.50 | 02/15/23 |
| 3 | Jacket | 812.00 | 217 | 200.00 | 03/20/23 |
Based on 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, view 100+ formulas, 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.