SUMIFS

Formulas / SUMIFS
To sum cells in a range that meet one or more conditions.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range - the range of cells to sum
  • range1 - the first range to evaluate criteria on
  • criteria1 - the condition applied to range1
  • range2 - [OPTIONAL] an additional range to evaluate criteria on
  • criteria2 - [OPTIONAL] an additional criteria applied to range2

Examples

  • =SUMIFS(A1:A10,A1:A10,">5")

    In this example, SUMIFS returns the sum of cells that are greater than 5.

  • =SUMIFS(A1:A10,B1:B10,"red")

    Here, the SUMIFS function returns the sum of numbers in A1:A10 when B1:B10 is the color "red".

  • =SUMIFS(A1:A10,A1:A10,">5",B1:B10,"red")

    In this example, SUMIFS returns the sum of cells in A1:A10 greater than 5 when B1:B10 is "red"

  • =SUMIFS(F5:F15,C5:C15,"red")

    SUMIFS returns the sum of column F values when the column C values are "red" in this example.

  • =SUMIFS(F5:F15,C5:C15,"red",D5:D15,"TX")

    In this example, SUMIFS returns the sum of column F values when the column C values are "red" and the column D states are "TX", which stands for Texas (TX)

Summary

The SUMIFS function adds all of its arguments that meet multiple criteria.

  • The SUMIFS function sums cells in a range that meet one or more conditions.
  • The SUMIFS function works by applying multiple criteria using the AND logic operator.
  • The SUMIFS function supports wildcards for partial matching.
  • The SUMIFS is designed to handle up to 127 different conditions.
  • The SUMIFS function is versatile: it may be used to sum cells based on dates, text values, and numbers.
  • Note that additional ranges must match the number of rows and columns as sum_range.


Frequently Asked Questions

What is the SUMIFS function?
The SUMIFS function sums cells in a range that meet one or more conditions. The sum of cells in the range must meet the conditions provided.
What logical operators does the SUMIFS function support?
The SUMIFS function supports logical operators >, <, <>, and =, which can be used to apply different conditions.
Can SUMIFS be used with wildcards?
SUMIFS can be used with wildcards (* and ?).
What do wildcards and logical operators allow for with SUMIFS?
When you combine wildcards and logical operators in SUMIFS, you can do partial matching operations.

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.