SUMIF vs SUMIFS in Excel

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:

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:

② 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:

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:

② 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:

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:

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:

② 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:

💬 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 Excel-related problems and solutions.

Related Article

• Excel 3D SUMIF for Multiple Worksheets