Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

SUMIF vs SUMIFS in Excel (A Comparative Analysis)

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.

sumif vs sumifs


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.

Dataset for Using 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)

Using SUMIF Function for Single Criteria

  • Then, press Enter.

Getting Total Based on Criteria Using SUMIF Function in Excel

🔎 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.

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.

Dataset for Applying SUMIFS Function

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)

Applying SUMIFS Function to Get Summation Based on Multiple Criteria in Excel

  • Thirdly, press Enter to get the Total Sales.

Getting Total Sales by Applying SUMIFS Function

🔎 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.

Dataset to Check Flexibility of SUMIF vs SUMIFS

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)

Using SUMIF Function to Check Flexibility for SUMIF vs SUMIFS

  • After that, press Enter.

Getting Results from SUMIF Function in Excel

Here, in the SUMIF function, we selected cell range D5:D10 as the range, Cell C13 as the criteria, and cell range G5:G10 as sum_range. The formula returns the summation of values from the sum_range that match the criteria.

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)

Employing SUMIFS to Check Flexibility for SUMIF vs SUMIFS in Excel

  • Thirdly, press Enter to get the result.

Getting Result from SUMIFS Function

Here, in the SUMIFS function, we selected cell range G5:G10 as sum_range, D5:D10 as criteria_range1, and Cell C13 as criteria1. Now, the formula returns the summation of values from the sum_range that match the criteria.

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.

Dataset for Choosing Function from SUMIF vs SUMIFS

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)

Using SUMIFS Function to Choose Function from SUMIF vs SUMIFS

  • Thirdly, press Enter.

Pressing Enter to Get Result

Here, in the SUMIFS function, we selected cell range G5:G10 as sum_range, D5:D10 as criteria_range1 and Cell C13 as criteria1, F5:F10 as criteria_range2 and C14 as criteria2. Now, the formula returns the summation of values from the sum_range that match both of the criteria.

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.


Summary of SUMIF vs SUMIFS in Excel

From the above discussion, we can summarize it into 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.


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