SUMIF Function

What Is The SUMIF Function In Excel?

In math, we know the sum is the total of values you added. Therefore we know that sum is related to addition. Everyone learns to add in their primary years of school. When working with addition, sometimes there might be an occasion when you only want to add certain numbers.

The SUMIF function on Excel allows you to add your data but be able to distinguish what exactly you want to have added. The function will sum the values in the range that you want in your formula. 

When working on Excel, your spreadsheets may have endless amounts of data on them. Therefore, it may take a lot of work if you want to add certain values specifically. 

Picking out the values you want to add can be time-consuming, and you run the risk of leaving some values out. Excel provides this function to help you out in these situations. In addition, using the formula will give you more efficient and accurate sum results.

There is another function in Excel called SUM. This function is fairly similar to the SUMIF function, although they have one difference. 

The sum function allows you to add all your selected values from your data. SUMIF adds all your values as well. When using SUMIF, you can specify exactly what you want there to be added to your data. 

A simple example is if you have a set of values you want to know the sum for. Although you only want to add the values that are greater than 10. Your SUMIF formula will allow you to specify that, and it will sum all your values above 10. 

The SUM function does not allow you to specify which range of values you want to be summed.

This can be helpful when you sum up certain sales. Therefore it can be helpful to financial analysts, accountants, or sales analysts. 

SUMIF Formula in Excel

Some functions do not require all the arguments to be entered when working with Excel functions. Therefore it is important to know which argument is optional and which are required. Also, it is important to know what the arguments entail to avoid any errors down the road. 

The formula for the function should be as follows: 

SUMIF(range,criteria,[sum_range]) 

The arguments for the function are as follows:

• range - this argument is required 
• criteria - required for the SUMIF function 
• sum_range - this is an optional argument

When working with the range argument, you will select the range of cells in the worksheet that you want to be evaluated. This can include numbers, names, arrays, and references that have numbers. If there are dates, they must be in standard Excel format, and any numbers in text format will be ignored. 

The criteria can be numbers, expressions, cell references, and text. However, it is important to know that any logical and mathematical criteria should start and end with double quotation marks. If it is just a number quotation, marks are not required. 

For the last argument, sum_range will include the cells to add if there are other ones besides the ones included in the range argument. 

If you do not want to include an entry for this argument, Excel will just sum the cells from the range and the ones to which the criteria are applied. 

Before we start using the function, it is always good to know the basics of the formula. Also, it is good to know that whenever you want to use a function in Excel, you need to start it off with an equal sign to get recognized. 

How to Use the SUMIF Excel Function?

We will apply it to two examples to get an idea of how the function works. One of the examples will be on the data sheet of a real estate agent. The second will be on something as simple as finding the total sum of different foods within the farmers market.

1. Example 

To start this example, we will have a total of four property values and commission values. Therefore, our data in Excel should look like the following: 

Now you will click on the desired cell on which you want your formula to be calculated. For our first calculation, we will want the sum of the commissions for all the properties with a value of over two hundred thousand. 

If you have different numbers, pick the preference of numbers for your data. For example, your range should be the property values, the criteria will be over two hundred thousand, and the sum range would be the commission values. 

As you see, the cells will be highlighted for each entered argument accordingly. Also, when the criteria are entered, they start with double quotation marks and end with double quotation marks due to the greater than sign. Finally, once your formula is complete, click enter to compute.

Now let's try the example without using our optional argument. First, we’ll find the sum of the property values, which is over one hundred fifty thousand. 

As you see, the sum range was not required. Therefore, the function was calculated without any error. Also, it is always good to label your results if you will have multiple ones to be organized. 

2. Example

For our second example, as mentioned, we will calculate the sum of certain products within the local farmers’ market. We will have various fruits and vegetables. 

Our data will look like the following:

We will find the sum of fruits, foods that end in “es,” and foods that do not have a category. The results will look as follows:

For the first calculations from the sum of fruits, you can see that we used the category options for the range argument. For the criteria, we wanted to find the fruits; therefore, we selected the cell with the word fruit, and for sum_range, we selected all the sales.

We do the process slightly differently for the “es” foods. Instead of the range being the sections labeled category, we used food because our main goal is to find the foods that end in “es.” Therefore, when entering the criteria argument, specify what you seek. 

We achieve the last result by entering double quotation marks in the criteria argument. We enter the quotation mark because if we are just to leave the argument blank, then we would not get accurate results. 

Researched and Authored by Sandra Martinez | LinkedIn

Reviewed and Edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• NPV Function
• OR Function
• PMT Function
• RATE Function
• SQRT Function