A.N.M. Mohaimen Shanto
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 saviors. 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 suitable examples and explanations.
Download Practice Workbook
You can download the practice workbook from here.
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. This function is available in all versions of Microsoft Excel.
The syntax of SUMIF function is:
=SUMIF(range,criteria,[sum_range])Take a look at the following table to have a better understanding:
|
ARGUMENTS |
REQUIRED/OPTIONAL |
VALUE |
|
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, 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. |
Example: Use SUMIF Function to Get Summation Based on Single Criteria
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 example, we are going to use the following dataset. Here, we have some Salespersons’ names, their selling Products, and Total Sales. Suppose, you want to find the Total Sales for a Salesperson named John. Now, We are going to calculate it using the SUMIF function.
Let’s see the steps.
Steps:
- Firstly, select the cell where you want the Total. Here, I selected Cell C17.
- Next, in Cell C17 write the following formula.
=SUMIF(C5:C12,C15,D5:D12)
- Then, press Enter.
🔎 How Does the Formula Work?
- Here, in the SUMIF function, we selected cell range C5:C12 as the range. This cell range refers to the Salesperson column.
- Then, we selected Cell C15 as the criteria. This cell contains Jhon.
- After that, we selected cell range D5:D12 as sum_range. This cell range refers to the Total Sales column.
- Finally, the formula returns the summation of values from the sum_range that match the criteria.
Read More: SUMIF between Two Values in Excel (An Easy Way)
Introduction to Excel SUMIFS Function
The SUMIFS function sums 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 wildcards (*,?) for partial matching.
Unlike the SUMIF function, this one comes in handy when you require multiple criteria to evaluate. This function was introduced in Excel 2007 and it is available in every later version of Microsoft Excel.
The syntax of SUMIFS function is:
=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2],[criteria2],…)Take a look at this table to have a better understanding:
| ARGUMENTS | REQUIRED/OPTIONAL | VALUE |
| 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 | These are additional ranges that denote criteria. One can insert 127 range/criteria pairs. |
Example: Apply SUMIFS Function to Get Summation Based on Multiple Criteria in Excel
Similar to the SUMIF function, SUMIFS has one sum range. That means all the additions 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 example, we are using this dataset. This dataset contains 4 columns, Invoice No, Salesperson, Product, and Sales. Suppose, you want to calculate the Total Sales of a Salesperson named Jimmy for TV. Now, we will calculate this using the SUMIFS function in Excel.
Let me show you the steps.
Steps:
- Firstly, select the cell where you want to calculate the Total Sales.
- Secondly, write the following formula in that selected cell.
=SUMIFS(E5:E13,C5:C13,C16,D5:D13,C17)
- Thirdly, press Enter to get the Total Sales.
🔎 How Does the Formula Work?
- Here, in the SUMIFS function, we selected cell range E5:E13 as sum_range.
- Then, we selected cell range C5:C13 as criteria_range1 and Cell C16 as criteria1.
- After that, I selected cell range D5:D13 as criteria_range2 and C17 as criteria2.
- Finally, the formula returns the summation of values from the sum_range that match both of the criteria.
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 the following dataset. Here, we have the names of some Projects, their Start Date, End Date, 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 proof of that.
To begin with, we will solve this problem with the SUMIF function. Let’s see the steps.
Steps:
- In the beginning, select the cell where you want the Total Bill. Here, I selected Cell C15.
- Then, in Cell C15 write the following formula.
=SUMIF(D5:D10,C13,G5:G10)
- After that, press Enter.
Now, you can also solve this problem using the SUMIFS function. Let me show you the steps.
Steps:
- Firstly, select the cell where you want the Total Bill. Here, I selected Cell C15.
- Secondly, in Cell C15 write the following formula.
=SUMIFS(G5:G10,D5:D10,C13)
- Thirdly, press Enter to get the result.
SUMIF vs SUMIFS: Which Excel Function to Choose?
According to us, the SUMIFS function is a 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, we will find the total bill of the projects finished before December 21 but the working hour is less than 200 hours.
Here we have multiple criteria. The first one is December 21 and the second one is the working hour is less than 200. Generally speaking, you can not solve these types of problems with the SUMIF function. It can’t take multiple criteria. But you can easily solve this with the SUMIFS function.
Let’s see the steps of this calculation.
Steps:
- Firstly, select the cell where you want the Total Bill.
- Secondly, write the following formula in that selected cell.
=SUMIFS(G5:G10,D5:D10,C13,F5:F10,C14)
- Thirdly, press Enter.
As you can see, we have successfully handled multiple criteria with the SUMIFS function. That’s why SUMIFS is more efficient than the SUMIF function in a lot of situations.
Takeaway of This Article
From the above discussion, we can summarize it in the following table:
| DIFFERENCES | 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 | criteria2 to criteria127,criteria_range1 to criteria_range127 |
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, we hope this tutorial has provided you with useful knowledge about SUMIF vs SUMIFS 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. Lastly, don’t forget to check our website ExcelDemy for various Excel-related problems and solutions.
