SUMIFS

Countifs, sumifs, and Averageifs are some of the most used formulas in Excel.

SUMIFS() allows you to sum the values within a specified range conditionally if criteria in other ranges are met.

SUMIFS Formula

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

Sumifs will sum the values in the range only if the values in the criteria range meets the criteria.

In other words,

=SUMIFS(range to sum up, 1st range to check, with this criteria 1, 2nd range to check, with this criteria 2, …)

Example

In the example above, there are two examples of SUMIFS(). The first one will add the values in cell range E2:E16, when the values in range B2:B16 are equal to “Wii”

In the second example the values in cell range E2:E16 will be added, when the values in range B2:B16 are equal to “Wii” and the values in range D2:D16 are equal to “Sports”

SUMIFS Advanced Formulas

Criteria Uses a Cell Reference

Many times, we need to sum up values when one condition is greater than a number in another cell.

In the example above, we use the SUMIFS() formula to add the values from range I2:I16 only when the values in range E2:E16 are greater than 1,200

This is great when you want to add the values based on a value in another cell. This also allows you to change the criteria dynamically.

Criteria Using Text Wildcards

=SUMIFS(range to sum up, 1st range to check, “P*”)

This example shows how your criteria neds to start with the letter “P”.

=SUMIFS(range to sum up, 1st range to check, “P?”)

This example shows how your criteria needs to start with the letter “P” and the second text character can be anything.