Excel SUMIFS Function

What is the Excel SUMIFS Function?

The SUMIFS function is a SUMIF function with more than one criteria. This function available for Excel 2007 or newer version, an improvement of the SUMIF function that handles only one criterion.

SUMIFS Syntax

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

sum_range, the range to be added, if all criteria met.

criteria_range1, the first range to be evaluated by criteria1

criteria1, the first criteria used to evaluate the data in criteria_range1.

criteria_range2, criteria2, optional, handles up to 127 pairs criteria_range and criteria.

How to Use SUMIFS Function in Excel

For example, there are data such as the image below.

Question #1, SUMIFS Multiple Criteria in Same Column (AND Criteria)

How much is the total sales for July 2, 2018, until July 4, 2018?

What is the number of criteria in the question above, one or two? The answer is the two criteria, sale on July 2, 2018, or after that AND sales of July 4, 2018, or earlier.

Which function is your choice? Many functions can be used, but the easiest one is to use the SUMIFS function.

Here is a formula to answer question #1

=SUMIFS(E2:E14,C2:C14,">=7/2/2018",C2:C14,"<=7/4/2018")

There is no BETWEEN operator in excel; the solution is to create two criteria and point to the same criteria_range, i.e., range C2:C14. The first criteria use “>=” operator and second use “<=” operator, numbers in the sum_range argument will be summed if both criteria return a TRUE value.

The result is as below.

Question #2, SUMIFS Multiple Criteria in Same Column (OR Criteria)

How much are the total sales before July 2, 2018, and after July 4, 2018?

Similar to question #1, asking for total sales on a specific date, but no BETWEEN. The number of criteria of the above question is 2; both point to the same criteria_range, the difference, the number will be added if one of the criteria is met. Can SUMIFS function answer this question?

Here is a formula to answer questions #2

=SUMIFS(E2:E14,C2:C14,"<7/2/2018",C2:C14,">7/4/2018")

The results are as shown below.

Why is the result zero? Shouldn’t the result is 52,002? Why SUMIFS function not working properly?

The SUMIFS function, could not handle questions with OR criteria, either in the same column or different columns. The number in sum_range argument will be added if all criteria return a TRUE value, if only one criterion is met then the number will not be added up.

The following questions could not be solved too by the SUMIFS function.

What are the total sales for the “West” and “East” areas?

The result is zero, why? Because SUMIFS uses AND for all its criteria. It’s mean, data in a cell in column A should be “West” and “East” simultaneously, and it is impossible.

Question #3, SUMIFS Multiple Criteria in Different Column (AND Criteria)

How much are the total sales of the iPhone X in West area?

There are two criteria, the first criteria “X iPhone”, point to criteria_range B2: B14. The second criteria “West” point to criteria_range A2: A14.

Here is a formula to answer questions #3.

=SUMIFS(E2:E14,A2:A14,"West",B2:B14,"iphone x")

There are five sales data for the area “West”, two sales data for iPhone X, but only one sales data for iPhone X in the West Area.

The result is as below.

SUMIFS works perfectly if all the criteria should be TRUE (AND Criteria)

Question #4, SUMIFS Multiple Criteria in Different Column (OR Criteria)

How much is the total sales for “East” area OR “iPhone X”

Maybe this question never asked in the sales data analysis, but this only to show that the SUMIFS function cannot answer questions with OR criteria.

Here is a formula to answer questions #4.

=SUMIFS(E2:E14,A2:A14,"East",B2:B14,"iphone 6")

The result should be 48.201, but the picture above shows different results. SUMIFS cannot answer the questions with OR criteria, either in the same or different columns.

SUMIFS Limitations

SUMIFS Example

Another article using SUMIF Function

Another Math Function

Another Logical Function