**SUMIFS vs. SUMIF**

Here is a table of differences between SUMIFS and SUMIF functions. The most noticeable difference is the number of criteria. The SUMIFS function can handle up to 127 criteria; the SUMIF function handles only one criterion.

**Questions for SUMIFS/SUMIF Function**

**Question #1, What is the total sales for sales greater than 500**

One criterion in the same column question.

**SUMIFS Solution**

The Formula

=SUMIFS(C2:C23,C2:C23,">500")

The Result

**SUMIF Solution**

The Formula

=SUMIF(C2:C23,">500")

The Result

The same result and no difference, but the SUMIF function use fewer arguments than the SUMIFS function. The sum_range argument in SUMIF function is optional; if the value is the same as the range argument, then the sum_range argument can be ignored.

Otherwise, the sum_range argument in SUMIFS function is mandatory, although the value is equivalent to the range argument, it must be written.

**Question #2, How much does it sell before July 15, 2018**

One criterion in the same column question.

**SUMIFS Solution**

The Formula

=SUMIFS(C2:C23,A2:A23,"<7/15/2018")

The Result

**SUMIF Solution**

The Formula

=SUMIF(A2:A23,"<7/15/2018",C2:C23)

The Result

No difference in the number of arguments used. Both SUMIFS and SUMIF function uses three arguments. The difference is the location of sum_range argument, in SUMIFS function sum_range is the first argument, while in SUMIF function sum_range is the last argument.

**Question #3, What is the total sales between July 10, 2018, and July 20, 2018**

Questions with two criteria in the same column, criteria #1 “>=7/10/2018” and criteria #2 “<=7/20/2018”

**SUMIFS Solution**

The Formula

=SUMIFS(C2:C23,A2:A23,">=7/10/2018",A2:A23,"<=7/20/2018")

The Result

**SUMIF Solution**

Not Applicable, the question has two criteria, SUMIF function unable to answer this question.

The SUMIF function can be used for questions with more than one criteria, by adding a helper column and logical function. For more details, please read the following articles

**Question #4, What is the total sales before July 10, 2018, and after July 20, 2018**

Two criteria in the same column question. Is the SUMIFS function able to answer this question?

**SUMIFS Solution**

Why the result 0? Should not the result 7.117.

The SUMIFS function can handle questions with more than one criteria, but only calculate the data for which all criteria are met. It’s impossible in a cell contains date less than July 10, 2018, and greater than July 20, 2018, at the same time

Although the SUMIFS function can handle the question with more than one criteria, not all questions can be handled. Especially multiple OR criteria questions, either in the same or different columns.

For more details about the SUMIFS limitations and solution, please read the article below:

**SUMIF Solution**

Same as the previous question. Not Applicable, the question has two criteria, but you can use a trick, adding a helper column and logical function makes the SUMIF function handle more than one criteria.

The following article explains in detail how to do the trick above.

**Question #5, What is the total sales between July 10, 2018, to July 20, 2018, for the “West” area**

Three criteria in different columns question. Criteria #1 “>=7/10/2018”, criteria #2 “<=7/20/2018” both are in the same column, criteria #3 “West” area in different columns.

**SUMIFS Solution**

The Formula

=SUMIFS(C2:C23,A2:A23,">=7/10/2018",A2:A23,"<=7/20/2018",B2:B23,"West")

The Result

It’s easy for the SUMIFS function to answer multiple AND criteria questions.

**SUMIF Solution**

Please read the article below, how making SUMIF function handles multiple AND criteria in different columns.