**The SUMIF Function in Excel – Quick View**

**Introduction to the 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 to evaluate by criteria. |

criteria |
Required | The criteria: an expression, a number, a text, a function, or a cell reference that defines which cells to add. |

sum range |
Optional | The cells to add to combine cells that are not defined in the range argument. |

**Note:**

- In the criteria, wildcard characters can be included: a question mark (?) to match a single character, an asterisk (*) to match a sequence of characters. Like 6?”, “apple*”, “*~?”
**sum_range**should be the same size and shape as the**range**.**The SUMIF**function can only have a single condition.

### Example 1 – Calculating a Sum with Numeric Criteria Using the SUMIF Function

To count the total sales, whose price was more than **$1000** in **H7**.

- Enter the formula in
**H7**.

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

**E5:E16** refers to theÂ **Sales **column.

**Formula Breakdown:**

**E5:E16**is the range for the sum operation.**“>1000”**is the criteria. If the sales value is more than**$1000,**Â it will be counted. Otherwise, it will be ignored.

- Press
**ENTER**. - The output is
**$26,700.**

### Example 2 – Finding a Sum with Text Criteria Using the SUMIF Function

Calculate the sales in the **Fruits ****Category**.

- Enter the formula in
**H8**.

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

**Formula Breakdown:**

**C5:C16**is the range to check the criteria.**“Fruits”**is the condition or criteria. It checks if the**Category**is**Fruits**.**E5:E16**is the sum range.

- Press
**ENTER**. - The output is
**$14,700**.

**Read More: **Excel SUMIFS with Not Equal to Text Criteria

### Example 3 – Use the SUMIF Function with Wildcard Characters for a Partial Match

To calculate the sum of the total sales of **Apples**.

- Enter the formula in
**H8**.

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

**Formula Breakdown:**

**“*Apples”**will find the name Apples or a name in which the first or last part is apples.

- Press
**ENTER**to see the output.

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

### Example 4 – Calculating the Sum with Date Criteria

To get the sum of sales of foods sold after **04/01/2021**.

- Enter the formula in
**H8**.

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

**Formula Breakdown:**

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

- Press
**ENTER**. - This is the output.

**Read More:** Excel SUMIF with Date Range

### Example 5 – Calculating the Sum with the OR Criteria in the SUMIF Formula

To calculate the total sales ofÂ **Vegetables**, or sales greater than **$1000**.

- Enter the formula in
**H8**.

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

**Formula Breakdown:**

**SUMIF(C5:C16, “Vegetables”, E5:E16)**Â finds the rows in the Category:**Vegetables**.- The plus sign
**(+)**is used for the**OR** **SUMIF(E5:E16,”>1000″, E5:E16)**finds the rows where Sales are greater than $1000.

- Press
**ENTER**. - This is the output.

### Example 6 – Applying the SUMIF with aÂ Criteria Array

To count the total sales in the **Category**Â **Fruits and Dairy:**

- Enter the formula in
**H8**.

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

- Press
**ENTER**. - This is the output.

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

### Example 7 – Using the SUMIF with Date Range (Month and Year) Criteria

Find the **Total Bill**.

** **

- Enter the formula in
**C13**.

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

- Press
**ENTER**. - This is the output.

**Read More: **[Fixed] Excel SUMIF Not Working

## SUMIF Vs SUMIFS in Excel: What’s the Difference?

Both the **SUMIF **and **SUMIFS** function add the values of all cells in a range that meet a given criterion, but:

- The
**SUMIF function**adds all cells in the range that match particular criteria. **The SUMIFS function**counts how many cells in a range meet a set of criteria.

To find **Sales of Apples** in **Branch 1:**

- Enter the formula in
**I5**.

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

- Press
**ENTER**. - This is the output.

The **SUMIF **function finds the finish date **Dec-21**, and calculates the total bill.

**Things to Remember**

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

**Download Practice Workbook**

## Excel SUMIF Function: Knowledge Hub

**<< Go Back to Excel Functions | Learn Excel**