While working with Microsoft Excel, the SUM function is one of the essential functions that everyone uses. We use this function in a lot of scenarios. But when it comes to summing up values based on criteria, the SUMIF and SUMIFS functions are our savior. You may think of which one best serves your purpose. In this tutorial, you will learn the difference between SUMIF vs SUMIFS functions in Excel with proper examples and explanations.
Download Practice Workbook
Download the following practice workbook.
Introduction to SUMIF Function in Excel
Now, the SUMIF function sums up a given range based on only one condition. It will add the values if the condition matches the given value ranges. If your condition matches, it will find the respective cells in the sum range and add them.
The Basic Syntax of SUMIF Function:
Take a look at the following table to have a better understanding:
Arguments  Required  Description 
range  Yes  The range of cells that you want to search by condition. The range of cells must be numbers or names, arrays, or references that have numbers. Blank and text values are ignored. 
criteria  Yes  The criteria are in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. 
sum_range  Optional  For the actual cells to add, we want to add cells other than those specified in the range argument. If the sum_range argument is removed, Excel adds the cells that are specified in the range argument. 
How Does the SUMIF Function Work?
Now, we have discussed the SUMIF function briefly. Itâ€™s time to show how it works.
We have two ranges in the SUMIF function argument. Here, the first one is the range that we will evaluate by our criteria. And the second one is the sum range from whence we will get our desired sum.
To demonstrate this, we going to use the following dataset:
Here, we have some salespersonsâ€™ name, their selling products, and total sales.
We are going to find the total sales of JohnÂ
đź“Ś StepsÂ
â‘ First, the following formula in Cell C14:
=SUMIF(C5:C12,"John",D5:D12)
â‘ˇ Then, press Enter.
As you can see, we have found the total sales of John using the SUMIF function.
Explanation of the above Example:
Now, in our formula, we selected Salesperson as Range and Total Sales as sum_range.
Then we have mentioned â€śJohnâ€ť as our criteria. It will search all the values from the Salesperson and add the Total Sales from there.
Introduction to SUMIFS Function in Excel
The SUMIFS function sum cells based on multiple criteria. SUMIFS can sum values when corresponding cells meet criteria based on dates, numbers, and text. It should be noted that we use logical operators (>,<,<>,=) to match conditions and the wildcards (*,?) for partial matching.
Unlike the SUMIF function, this one comes in handy when you require multiple criteria to evaluate.
The Basic Syntax of SUMIFS Function:
Take a look at this table to have a better understanding:
Arguments  Required  Description 
sum_range  Yes  Range of cells we want to sum based on conditions or criteria. 
Criteria_range1  Yes  Range of cells where we will apply the criteria or condition. 
Criteria1  Yes  Condition for the criteria_range1. 
Criteria_range2, criteria2, â€¦Â  Optional  Additional ranges and their associated criteria. You can enter up to 127 range/criteria pairs. 
How Does the SUMIFS Function Work?
Similar to the SUMIF function, SUMIFS has one sum range. That means all the addition will take place based on this range. Here, we can use multiple criteria. First, it will try to match values based on criteria1. If you have other conditions, it will take this into account and sum values accordingly.
To demonstrate this, we are using this dataset:
Here, we some salespersons name, their selling products, and Sales amount.
We are going to find total sales of Jimmy for the product TV
đź“Ś StepsÂ
â‘ First, the following formula in Cell C14:
=SUMIFS(E5:E13,C5:C13,"Jimmy",D5:D13,"TV")
â‘ˇ Then, press Enter.
As you can see, we have found the total sales of Jimmy for the product TV using the SUMIFS function.
Explanation of the above Example:
Now, letâ€™s break it down. Firstly, we have selected the sum_range, criteria_range1, criteria_range2.
Now, our first criteria were Jimmy. That means it will first find the salesperson Jimmy from the Salesperson column.
Then, our next criteria were TV. That means how much sales Jimmy generated from the product TV. From the values of Jimmy, it will search for TV in the Product column.
Finally, it will sum the sales of Jimmy for the product TV.
SUMIF vs SUMIFS: Flexibility in Excel Sum Operation
Now, you can not perform the SUMIFS operation with the SUMIF function. But you can easily perform SUMIFS instead of SUMIF. It will give you the same result. That means if you have single criteria, you can use the SUMIFS function.
Take a look at this dataset:
Here, we have some projects consisting of developers, start and finish dates, rate per hour, and total bills.
We are going to find the Total Bill of the projects finished before December 21.
Like we said earlier, you can use the SUMIFS function instead of SUMIF. Here we are giving you the proof of this:
To solve this problem with the SUMIF function, type the following formula in Cell C13:
=SUMIF(E5:E10,"<"&C12,H5:H10)
After that, press Enter.
As you can see we have found the total bill of the projects finished before December 21.
Now, you can also solve this by the SUMIFS function. Letâ€™s have a look:
First type the following formula in Cell C13:
=SUMIFS(H5:H10,E5:E10,"<"&C12)
Then press ENTER.
As you can see, we have successfully found to Total Bill of the projects finished before December 21 with the SUMIFS function.
So, you can use the SUMIFS function instead of SUMIF.
SUMIF vs SUMIFS: Which Excel Function to Choose?
According to us, the SUMIFS function is the more handy tool. It provides a similar output as the SUMIF does. As the SUMIFS function can handle multiple criteria, you can use it anywhere.
Now, from the previous dataset, is it possible to find the total bill of the projects finished before December 21 but work hours less than 200 hours?
Here we have multiple criteria. The first one is December 21 and the second one is work hours less than 200.
Generally speaking, you can not solve these types of problems by the SUMIF function. It canâ€™t take multiple criteria. But you can easily solve this with the SUMIFS function.
đź“Ś StepsÂ
â‘ First, type the following formula in Cell C14:
=SUMIFS(H5:H10,E5:E10,"<"&C12,G5:G10,"<"&C13)
â‘ˇ Then, press ENTER.
As you can see, we have successfully handled multiple criteria with the SUMIFS function. Thatâ€™s why SUMIFS is efficient than the SUMIF function in a lot of situations.
Summary: SUMIF vs SUMIFS in Excel
From the above discussion, we can summarize it into the following table:
Difference  SUMIF  SUMIFS  

Availability  All the versions  Excel 2007 or newer.  
Number of Criteria  Only one  Up to 127 Criteria  
Position of sum_range  In the Last Argument  Â As a first argument  
sum_range requirement  Optional  Required  
Optional Arguments  sum_range 

đź’¬ Things to Remember
âśŽ In the SUMIFS function, other criteria canâ€™t be in the same range as criteria1.
âśŽ Moreover, the criteria_range argument must contain the same number of rows and columns as the sum_range argument.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge about the difference between SUMIF and SUMIFS functions in Excel. So we recommend you learn and apply all these instructions to your dataset. Moreover, you can download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Indeed your valuable feedback keeps us motivated to create tutorials like this. Donâ€™t forget to check our website Exceldemy.com for various Excelrelated problems and solutions.