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.
- —[optional]
- criterion_range2
- criterion2
- ...

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:

Item | Quantity | Cost | Warehouse |
---|---|---|---|

Shirt | 26 | 20.00 | A |

Pants | 24 | 50.00 | A |

Socks | 10 | 10.00 | A |

Shirt | 18 | 25.00 | B |

Pants | 16 | 75.00 | B |

Socks | 46 | 15.00 | B |

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

Formula | Description | Result |
---|---|---|

=SUMIFS(Quantity:Quantity, Cost:Cost, >30,Warehouse:Warehouse,"A") | Sums the Quantity of any item from Warehouse "A" with a Cost over 30.00 | 24 |

=SUMIFS(Quantity:Quantity, Quantity:Quantity, >10, Cost:Cost,<20) | Sums the Quantity of anything over 10 with a Cost under 20.00 | 46 |

=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 |