SumIF Function in Excel

SumIF Function in Excel

SumIF is one of the basic functions in Microsoft Excel to sum cells that meet a single criteria. It is a built-in function in Excel, which helps to sum cells based on dates, numbers, and texts having specific criteria.

SumIF is a worksheet function that is entered in a worksheet cell as part of a formula. SUMIF supports logical operators that include (>,<,<>,=) and special characters (*,?).  click here for more details.

Purpose

Sum numbers in a range meeting supplied criteria.

Return Value

Supplies sum value.

Syntax

Similar to all other formulas of Excel, its syntax starts with “=”

The syntax of SUMIF function looks like this-

=SUMIF(range, criteria, [sum_range])

Arguments  

Range – The range of cells that you are looking to implement the criteria against. 

Criteria – It is used to determine which cells to add.

Sum_Range – Optional. It is used to add cells together. If sum_range is excluded, the cells in range are added together instead.  for further info, visit :
https://ittraining.iu.edu/training/browse.aspx?group=EXCEL

SumIF Function in Excel

Usage notes 

When sum_range is excluded, the cells in range will be added together.

All the text criteria, or criteria that has math symbols, should be enclosed in double quotation marks (“).

On the other hand, numeric criteria does not need any quotation marks to be supplied.

The special characters such as “?”, “*” can also be used in criteria.

To located an exact question mark or asterisk, you can use a tilde (~) in the beginning of question mark or asterisk (i.e. ~?, ~*).

Use of SumIF Function

By using SumIF formula in Excel, we are able to compute the sum of multiple data depending on desired conditions. As the name suggests, this function, sums-up the data when the exact criteria is met. This function has 3 arguments that includes range of data, criteria, and sum_range. We have to select all these 3 arguments to find out the desired results. 

How to Use SumIf in Excel? 

Let’s understand SUMIF function with an example. We have created the following worksheet that has customer records with fields like, Names, Items, Price and Paid?. 

In this example, we want to see the total amount paid by customers and the total amount they still have to pay. For this, we will have to add two more labels to this worksheet i.e. Total Amount Received, and On Credit.

Now our spreadsheet is ready to write SUMIF formula to find Paid Amount, that provides the sum of the amount which has been paid by the customers.

Syntax:

SUMIF(range, criteria, sum_range)

Formula:

=SUMIF(D2:D11, “Yes”, C2:C11)

The formula, D2:D11 holds keywords “Yes” and “No”, which describes whether the customer has paid the amount or they still have to pay, C2:C11 holds the price of different products.

When we will apply this formula, it will display the total amount paid by the customers by finding ‘Yes’ keyword in Paid? column and will add value in the Price Column. It will evaluate that 774$ has been paid, as given in the screen shot below.

Let’s apply same formula to check the amount customers still need to pay. For this, we require little change in this formula. Now this function will search “No” in paid? Column and will sump up comparing values in Price column. 

Use the formula as given below

=SUMIF(D2:D11, “No”, C2:C11)

It will show sum of amount which is still on credit as you can see in the snapshot given below.