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.

**Table of Contents**hide

## Download Practice Workbook

**Introduction to Excel SUMIF Function: Syntax & Arguments**

**Summary:**

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

**Syntax:**

**=SUMIF (range, criteria, [sum_range])****Arguments:**

Argument | Required/Optional | Explanation |
---|---|---|

range |
Required | The range of cells that we want to be evaluated by criteria. |

criteria |
Required | The criteria are in the form of an expression, a number a text, a function, or a cell reference, that defines which cells are to add. |

sum range |
Optional | The actual cells to add if we need to combine cells other than those defined in the range argument. |

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

## 7 Examples of Using SUMIF Function in Excel

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.

`=SUMIF(E5:E16,">1000")`

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

**Formula Explanation:**

- In this formula,
**E5:E16**is the range where the sum operation will be performed. **“>1000”**is the criteria. So, if the sales value is more than**$1000**then it will be counted otherwise it will be ignored.

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

**Read More:** **51 Mostly Used Math and Trig Functions in Excel**

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

`=SUMIF(C5:C16,"Fruits",E5:E16)`

**Formula Explanation:**

- Here
**C5:C16**is the range where we will check our criteria. **“Fruits”**is the condition or criteria. We are checking if the**Category**is**Fruits**or not.- Lastly,
**E5:E16**is the sum range where we will perform the sum operation of the selected rows.

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

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

`=SUMIF(B5:B16,"*Apples",E5:E16)`

**Formula Explanation:**

**“*Apples”**will find out the data where the**Food**name will be Apples or the first or last part of the food name is apples.

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

**Read More:** **44 Mathematical Functions in Excel (Download Free PDF)**

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

`=SUMIF(D5:D16,">"&DATE(2021,4,1),E5:E16)`

**Formula Explanation:**

**“>”&DATE(2021,4,1)**this portion is our criteria. Firstly,**“>”**is used to find the greater dates. Then ampersand (**&)**is used to concatenate the formula and text. The**DATE**function is used to give date input.- The
**DATE**function in Excel accepts three arguments: year, month, and day. If you want to know more about this function you can check this Link

- Again, press
**ENTER**. - Eventually, the output is like this.

**Similar Readings**

**How to Use MMULT Function in Excel (6 Examples)****Use TRUNC Function in Excel (4 Examples)****How to Use TAN Function in Excel (6 Examples)****Use Excel QUOTIENT Function (4 Suitable Examples)****How to Use LOG Function in Excel (with Real-Life Examples)**

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

`=SUMIF(C5:C16,"Vegetables",E5:E16)+SUMIF(E5:E16,">1000",E5:E16)`

**Formula Explanation:**

**SUMIF(C5:C16, “Vegetables”, E5:E16)**this part will find the rows where Category is equal to**Vegetables**.- Plus sign
**(+)**is used for the**OR** **SUMIF(E5:E16,”>1000″, E5:E16)**this part will find the rows where Sales are greater than $1000.

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

`=SUM(SUMIF(C5:C16,{"Fruits","Dairy"},E5:E16))`

- Similarly, press
**ENTER**and get the output like this.

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

`=SUMIF(D5:D10,"="&C12,G5:G10)`

- Secondly, press
**ENTER** - Eventually, get the output like this.

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

`=SUMIFS(F5:F16,B5:B16,"Apples",D5:D16,"Branch 1")`

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

## Excel SUMIF Function: Knowledge Hub

**Use Excel SUMIF to Sum Values Greater Than 0****Use SUMIF Function to Sum Not Blank Cells in Excel****Sum If Cell Contains Number and Text****Use 3D SUMIF for Multiple Worksheets****[Fixed!] Excel SUMIF Not Working**

**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. Also, we invite you to our blog **ExcelDemy** for more such discussions.

## Related Articles

**How to Use Excel SUMPRODUCT Function (7 Suitable Examples)****VBA EXP Function in Excel (5 Examples)****How to Use RAND Function in Excel (5 Examples)****Use MOD Function in Excel (9 Suitable Examples)****How to Use ROUND Function in Excel (9 Examples)****Use INT Function in Excel (8 Suitable Examples)****How to Find Inverse Cosine of a Number in Excel (3 Easy Methods)**