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.

## Download the Practice WorkBook

**SUMIF Function in Excel (Quick View)**

**Excel SUMIF Function: Syntax & Arguments**

**Summary**

Adds the cells specified by a given condition or criteria.

**Syntax**

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

**Arguments**

Argument |
Required or Optional |
Value |
---|---|---|

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

criteria |
Required | 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 | The actual cells to add if we need to combine cells other than those defined in the range argument. |

**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 the SUMIF Function in Excel (N Examples)**

**Example 1: Calculate Sum with Numeric Criteria Using SUMIF Function**

Using the **SUM 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.

**Step 1:** Enter the formula in cell **H7 **and press **Enter**

`=SUMIF(E4:E19,">1000")`

**Formula Explanation**

- In this formula,
**E4:E19**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.

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

**Example 2: Calculate Sum with Text Criteria Using SUMIF Function**

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

**Step 1:**Â Enter the formula in cell **H7 **and press **Enter**

`=SUMIF(C4:C19,"Fruits",E4:E19)`

**Formula Explanation**

- Here
**C4:C19**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,
**E4:E19**is the sum range where we will perform the sum operation of the selected rows.

**SUM Function with Wildcard Characters**

In the criteria argument, we can also utilize wildcard characters in the **SUM **function. Letâ€™s assume we want to calculate the sum of the total sales of those foods named Apples.

**Step 1:** Enter the formula in cell **H7 **and press **Enter**

`=SUMIF(B4:B19,"*Apples",E4:E19)`

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

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

**Example 3: Calculate Sum with Date Criteria Using SUMIF Function**

The **SUM **function is also applicable for using data conditions. Letâ€™s say that we want to get the sum of sales of those foods where the date is after 04/01/2021.

**Step 1:** Enter the formula in cell **H7** and press **Enter**

`=SUMIF(D4:D19,">"&DATE(2021,4,1),E4:E19)`

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

**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 Excel LOG Function (5 Easy Methods)**

**Example 4: Calculate Sum with OR Criteria Using SUMIF Function**

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 **SUM **function. Letâ€™s assume we want to calculate the total sales where Category is Vegetables, or each sale is greater than $1000.

**Step 1:** Enter the formula in cell **H7 **and press **Enter**

`=SUMIF(C4:C19,"Vegetables",E4:E19)+SUMIF(E4:E19,">1000",E4:E19)`

**Formula Explanation:**

**SUMIF(C4:C19, â€śVegetablesâ€ť, E4:E19)**this part will find the rows where Category is equal to Vegetables.- Plus sign
**(+)**is used for the**OR**operation. **SUMIF(E4:E19,â€ť>1000â€ł, E4:E19)**this part will find the rows where Sales are greater than $1000.

**Example 5: SUMIF with an Array Argument**

In the **SUMIF **function, we use the array argument as a condition. 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 **SUMIF **function.

**Step 1:** Enter the formula in cell **H7 **and press **Enter**

`=SUM(SUMIF(C4:C19,{"Fruits","Dairy"},E4:E19))`

**Formula Explanation**

**SUMIF(C4:C19,{â€śFruitsâ€ť, â€śDairyâ€ť}, E4:E19)**this part will find the total sales individually for Fruits and Dairy. Like $20400.00 for**Fruits**and $9100.00 for**Dairy**.- Then SUM function will return the total of these two categories.

**Things to Remember**

Common Errors |
When they show |
---|---|

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

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

## Related Articles

**How to Use SUMPRODUCT Function in Excel (4 Examples)****Use RAND function in Excel (5 Examples)****How to Use MOD Function in Excel (9 Suitable Examples)****Use ROUND Function in Excel (With 9 Examples)****How to Use INT Function in Excel (With 8 Examples)****Solving equations in Excel (polynomial, cubic, quadratic, & linear)**