Adds numbers within a range when all given conditions in their respective ranges are met.
Syntax
SUMIFS(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])
- range—The cells to add, belonging to rows that meet all the criteria.
- criterion_range1—The cells to evaluate by the criterion.
- criterion1—The condition for cell values in the criterion range to match; for example: 15, "Hello World!", or >25.
- criterion_range2, criterion2, ... —[optional]Additional ranges and criteria to evaluate.
Sample usage
SUMIFS(Cost:Cost, Quantity:Quantity, >25, [Task Name]:[Task Name], "Task A")
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).
- All evaluated criteria must be true for the number in the corresponding range to be added.
Examples
This example references the following sheet information:
Item | Quantity | Cost | Warehouse |
---|---|---|---|
Item Shirt | Quantity 26 | Cost 20.00 | Warehouse A |
Item Pants | Quantity 24 | Cost 50.00 | Warehouse A |
Item Socks | Quantity 10 | Cost 10.00 | Warehouse A |
Item Shirt | Quantity 18 | Cost 25.00 | Warehouse B |
Item Pants | Quantity 16 | Cost 75.00 | Warehouse B |
Item Socks | Quantity 46 | Cost 15.00 | Warehouse B |
Based on the table above, here are some examples of using SUMIFS in a sheet:
Formula | Description | Result |
---|---|---|
Formula =SUMIFS(Quantity:Quantity, Cost:Cost, >30,Warehouse:Warehouse,"A") | Description Sums the quantity of any item from warehouse "A" with a cost over 30.00 | Result 24 |
Formula =SUMIFS(Quantity:Quantity, Quantity:Quantity, >10, Cost:Cost,<20) | Description Sums the quantity of anything over 10 with a cost under 20.00 | Result 46 |
Formula =SUMIFS(Cost:Cost, Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks")) | Description Sums the cost of anything with a quantity over 15 from warehouse "A", where the Item is either "Shirt" or "Socks" Note that this formula also uses the OR Function and @cell parameter. | Result 20.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.