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:

=SUMIF(range, criteria, [sum_range])

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:

dataset for sumif vs sumifs in excel

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)

sumif formula for sumif vs sumifs in excel

Then, press Enter.

result of sumif in excel

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.

sumif ranges for sumif vs sumifs

Then we have mentioned “John” as our criteria. It will search all the values from the Salesperson and add the Total Sales from there.

sumif values search in excel


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:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)

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:

dataset for sumifs to demonstrate between sumif vs sumifs in excel

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")

sumifs formula for sumifs vs sumif

Then, press Enter.

result of sumifs function

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.

sumif vs sumifs in excel: sumifs ranges

Now, our first criteria were Jimmy. That means it will first find the salesperson Jimmy from the Salesperson column.

find jimmy from salesperson

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.

product TV in the given range for jimmy

Finally, it will sum the sales of Jimmy for the product TV.

finally found the sales amount


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:

dataset for sumif vs sumifs

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)

try to solve with sumif

After that, press Enter.

result after entering the function in excel

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)

trying to solve with sumifs

Then press ENTER.

result of sumifs function

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)

sumif vs sumifs: example 2

Then, press ENTER.

why sumifs is better than sumif

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
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, 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

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo