How to Use SUMIF Function in Excel (7 Suitable Examples)

In terms of calculating the summation of some numbers sometimes we may need to apply conditions or criteria. MS Excel helps us with these kinds of problems by providing another powerful function named SUMIF. This article will share the complete idea of how the SUMIF function works in Excel autonomously and then with other Excel functions.

SUMIF Function in Excel (Quick View)

The following image is a quick view of the Excel SUMIF function.

Introduction to Excel SUMIF Function: Syntax & Arguments

Summary:

This function adds the cells specified by a given condition or criteria.

Syntax:

Arguments:

Versions:

Excel 2007 to onwards.

Note:

• In criteria, wildcard characters can be included – a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. Like 6?”, “apple*”, “*~?”
  • Here question mark (?) will be used for matching any single character.
  • An asterisk (*) will be used to match any sequence of characters. Using this method, we can find out any text or string by matching any substring. Like “*Apples” we can find the words like Pineapples or any other words where the last portion is “Apples”.
• sum_range should be the same size and shape as the range.
• SUMIF function only supports a single condition.

How to Use SUMIF Function in Excel: 7 Practical Examples

Excel offers different ways to use the SUMIF function according to requirements. The syntax varies according to the use of this function. We just need to follow some simple steps in every method or example.

Example 1: Calculating Sum with Numeric Criteria Using SUMIF Function

Using the SUMIF function, we can calculate the sum with the numeric conditions. For showing the process let’s assume we have a dataset of some Foods with their name, category, date, and sales. Now we will count the total sales where every price was more than $1000 in the H7 cell.

• Firstly, write the formula in the H7 cell like this.

Here, E5:E16 refers to the column of Sales.

• Secondly, press ENTER.
• Eventually, we’ll get the output as $26,700.

Example 2: Finding Sum with Text Criteria Using SUMIF Function

Now let’s see how to calculate the sum using text criteria. Here our concern is to calculate sales from the dataset where the Category will be Fruits.

• So, firstly, write the formula in the H8 cell like this.

• Secondly, press ENTER, and consequently, the output will be $14,700.

Read More: Excel SUMIFS with Not Equal to Text Criteria

Example 3: Use of SUMIF Function with Wildcard Characters for Partial Match

In the criteria argument, we can also utilize wildcard characters in the SUMIF function. Let’s assume we want to calculate the sum of the total sales of those foods named Apples. So, in the H8 cell, write the formula like this.

• Similarly, press ENTER to get the output as $5,400.

Read More: [Fixed!] Excel SUMIF with Wildcard Not Working

Example 4: Calculating Sum with Date Criteria

The SUMIF function is also applicable for using date conditions. Let’s say that we want to get the sum of sales of those foods where the date is after 04/01/2021. As we want to calculate the sum in the H8 cell, similarly, to before, write the formula in the H8 cell like this.

• Again, press ENTER.
• Eventually, the output is like this.

Read More: Excel SUMIF with Date Range

Example 5: Calculating Sum with OR Criteria in SUMIF Formula

OR logic means if any logic or condition is true from the given logic then it will return true. We can use this logic using the SUMIF function. Let’s assume we want to calculate the total sales where Category is Vegetables, or each sale is greater than $1000.

• Write the formula in the H8 cell like this.

• Similarly, press ENTER and get the output like this.

Example 6: Applying SUMIF with an Array Criteria

In the SUMIF function, we use the array argument as a condition. An array argument is nothing but an array of some elements in any function’s parameter. Like: {“A”, “B”, “C”} etc. Now here we will count the total sales where Category is Fruits and Dairy using the SUMIF function.

• So, write the formula in the H8 cell.

• Similarly, press ENTER and get the output like this.

Read More: How to Use 3D SUMIF for Multiple Worksheets in Excel

Example 7: Using SUMIF with Date Range (Month and Year) Criteria

We can use the SUMIF function where we need to calculate the sum within a range of Month and Year. In the following dataset, we have column headers as Project, Start Date, Finish Date, Rate Per Hour, Worked Hour, and Total Bill. Suppose, in the C13 cell we need to find out Total Bill.

• Firstly, write the formula in the C13 cell like this.

• Secondly, press ENTER
• Eventually, get the output like this.

Read More: [Fixed] Excel SUMIF Not Working

SUMIF Vs SUMIFS in Excel: Where’s the Difference?

The SUMIF and SUMIFS functions in Excel both add up the values of all the cells in a range that satisfy a given criterion, but they do so in somewhat different ways:

• The SUMIF function adds up all the cells in a range that match particular criteria.
• The SUMIFS function counts how many cells in a range satisfy a set of criteria.

Suppose, we need to find out Sales of Apples in Branch 1. Here, we have two criteria which are Apples and Branch 1. Eventually, in this case, we need to use the SUMIFS function.

• Firstly, write the formula in the I5 cell like this.

• Press ENTER and get the output like this.

As we can see, we are successful in finding the total bills of the projects finished on December 21.
Importantly, here the SUMIF function finds the finish date Dec-21, and eventually, after that, adds the total bill according to that.

Things to Remember

The SUMIF function returns incorrect results (#VALUE! error) when you use it to match strings longer than 255 characters or to the string.

Download Practice Workbook

Conclusion

This is all about the SUMIF function and its different applications. Overall, in terms of working with time, we need this function for various purposes. Eventually, we have shown multiple methods with their respective examples but there can be many other iterations depending on numerous situations. If you have any other method of utilizing this function, then please feel free to share it with us.

Excel SUMIF Function: Knowledge Hub

<< Go Back to Excel Functions | Learn Excel