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.
