Excel Function: SUMIFS

Last Updated: April 27, 2023
puneet-gogia-excel-champs

- Written by Puneet

SUMIFS returns the sum of the numbers which meet multiple conditions you specify. Yes, you can specify multiple conditions with to test. Unlike SUMIF, you can set multiple conditions and can sum only those values which meet all those conditions.

sumifs-function

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

  • [sum_range]: A range of cells & values which you want to sum.
  • criteria_range1: A range from which you want to test criteria.
  • criteria1: A criteria which can be a number, text, expression, cell reference, or a function.
  • [criteria_range2]: A range from which you want to test criteria.
  • [criteria2]: A criteria which can be a number, text, expression, cell reference, or function.

Example

In the below example, we have used three different criteria to check from columns A, B, and C respectively, and it has returned the sum of the values from column E which meet those criteria.

three-different-criteria-for-sumifs

In the below example, we have used wildcard characters to specify three different criteria.

wild-characters-with-sumif
  • The first criteria range is A2:A9 and the criteria in the cell A12 will only sum cells that have the alphabet O.
  • The second criterion range is B2:B9 and criteria in cell B12 which will only sum cells that have alphabet P. It will apply to all the cells which met condition 1.
  • The third criterion range is C2:C9 and criterion in cell C12 which will sum cells with any type of value. It will apply to all the cells which met conditions 1 and 2.

Notes

  1. The size of the criteria range and sum range should be of the same size.
  2. If you skip specifying the SUM range, it will SUM the values from the criteria range that meet the criteria.