# SUMIF function in Excel: Learn with EXAMPLE

## What is SUMIF Function?

SUMIF is the function used to sum the values according to a single criterion. Using this function, you can find the sum of numbers applying a condition within a range. This function comes under Math & Trigonometry functions. Similar to the name, this will sum if the criteria given is satisfied. This function is used to find the sum of particular numbers within a large data set.

In this tutorial, you will learn-

• What is SUMIF Function?
• How to use the SUMIF function?
• Formula of MS SUMIF
• Example 1: Using SUMIF
• Example 2: How to use SUMIF with comparison operators
• Example 3: How to use SUMIF with dates

## How to use the SUMIF function?

SUMIF will sum the range of cells according to the criteria specified. The range of cells to where the condition should be applied, the constraint according to that the sum will calculate, the range of cell for which the sum to calculate according to the condition need specify within the formula.

## Formula of MS SUMIF

Generally, follow a format where the criteria and criteria range, etc. need to be provided.

`SUMIF(range,criteria,sum range)`
• Range: The range of cells which included the criteria
• Criteria: The condition that must be satisfied
• Sum range: The range of cells to add if the condition is satisfied.

## Example 1: Using SUMIF

Given the data table with order numbers and the bill amount. Need to find the sum of the bill amount for particular orders.

Step1) You want to find the sum of the bill amount for a particular brand order where the order number starts with ‘Amzcd.’

Step 2) Select the cell below to the bill amount and apply the formula to find the sum of bill amount only for the orders, which starts with ‘Amzcd.’

Step 3) The formula applied is

`'=SUMIF (B2: B12, "Amzcd*," C2: C12)'`
• B2: B12 is the range of cell where the criteria will be checking.
• ‘Amzcd*’ is the criteria applied where the starting of the order number.
• C2: C12 is the range of cells to add according to the criteria

Step 4) If you check the table, highlighted are the bill amounts summed according to the criteria given. The bill amount for every order starts with ‘Amzcd’ are selected, and the sum will find.

Step 5) Check the end of the column bill amount to see the sum of the bill amount, which satisfies the condition given.

## Example 2: How to use SUMIF with comparison operators

A set of comparison operators exists in excel. The SUMIF function can use along with comparison operators. Comparison operators can use to specifying the conditions within the SUMIF function. So if the comparison operation returns a true value, it will sum up the values.

Step 1) You have the table of data with name products, Qty, and price. How can you find the total price for the product for which the Qty is greater than and equal to 100?

Step 2) If you apply the criteria into logical format ‘Qty>=100’, and the SUMIF formula can form as below.

Step 3) The formula applied is

`=SUMIF (C2: C11,">=100", D2: D11)`
• C2: C11 is the range of cell where the criteria will be looking for
• ‘>=100’ is the condition which expressed with comparison operator greater than and equal to
• D2: D11 is the range to be summed according to the criteria

Step 4) Highlighted cells are the prices which satisfy the criteria, where the Qty>=100

Step 5) The sum of values are in the cell D13; it is the sum of prices for the products which have Qty greater than or equal to 100.

## Example 3: How to use SUMIF with dates

In this example, you will learn how to use SUMIF function with the date. The criteria can also give in the form of date.

Step 1) The table shows different account numbers, bill dates, and payment. The amount needs to be paid to the corresponding account within the bill date given. By using the SUMIF, you can try to find the sum of the amount to be paid for June.

Step 2) Since the date is with you, the criteria can form as ‘>5/31/2019’ which refer to the dates after May. Here the comparison operator and date are using at a time with SUMIF.

Step 3) The formula applied is

`=SUMIF (C2: C11,">5/31/2019", D2: D11)`
• C2: C11 is the range of cells where the dates are given and are the range where given criteria will be looking for
• ‘>5/31/2019’ is the criteria where date greater than ‘5/31/2019’
• D2: D11 range of numbers to add once the condition is satisfied

Step 4) Below are the cell values which satisfies the applied condition, and the sum of these values will find.

Step 5) The sum is there in D13. And is the total amount to be paid to multiple accounts for June.