SUMIFS Function

Adds numbers within a range that meet multiple specified criteria.
Sample Usage
SUMIFS(Cost:Cost, Quantity:Quantity, >25, [Task Name]:[Task Name], "Task A")
Syntax
SUMIFS(
  • range
  • criterion_range1
  • criterion1
  • [
    • criterion_range2
    • criterion2
    • ...
    ]
)
  • range
    The group of cells to sum, assuming they meet all criteria.
  • criterion_range1
    The group of cells to be evaluated by the criterion.
  • criterion1
    The condition that defines which numbers to add; for example: 15, "Hello World!", or >25.
    • criterion_range2
    • criterion2
    • ...
    —[optional]
    Additional ranges and criteria to have evaluated.
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:

ItemQuantityCostWarehouse
Shirt2620.00A
Pants2450.00A
Socks1010.00A
Shirt1825.00B
Pants1675.00B
Socks4615.00B

 

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

FormulaDescriptionResult
=SUMIFS(Quantity:Quantity, Cost:Cost, >30,Warehouse:Warehouse,"A")Sums the Quantity of any item from Warehouse "A" with a Cost over 30.0024
=SUMIFS(Quantity:Quantity, Quantity:Quantity, >10, Cost:Cost,<20)Sums the Quantity of anything over 10 with a Cost under 20.0046
=SUMIFS(Cost:Cost, Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks"))Sums the Cost of anything with a Quantity over 15 from Warehouse "A" where the Item is either "Shirt" or "Socks"
NOTE: This formula also uses the OR Function and @cell parameter.
20.00

 

Was this article helpful?
YesNo