The SUMIF Function is a built-in function in Excel that allows you sum up the values in a range that meet a single criterion. The SUMIF function was introduced to Excel in the year 2000, and its successor, the SUMIFS function, was introduced in the year 2007. They are largely the same, but whilst the SUMIF function can only check for a single condition / criterion, the SUMIFS function is able to check for multiple conditions / criteria. The advantage however is that the SUMIF function can easily be deployed when you need to filter data based on a single criterion.
To recap, the SUMIF Function is used to calculate the total sum of values in a range that meet a single condition, whilst the SUMIFS Function can be used to calculate the total sum of values in a range that meet one or more conditions. Our focus for this post is the SUMIF Function, we would take you through all you need to know about this function. For the SUMIFS Function, please check our guide here.
Syntax & Argument
The Syntax for the SUMIF Function is quite similar to that of the SUMIFS function, however it starts with the criteria range as opposed the sum_Range with the SUMIFS function.
=SUMIF(range, criteria, [sum_range])
There are three arguments for this function and 2 are required whilst one could be optional. Let’s look at their functions below:
- range: The range to evaluate based on the criteria
- criteria: Condition to test for, it could be text, number, cell reference or function that evaluates to a number or text.
- Sum_range: The range of cells to add up if the corresponding cells in the range being evaluated meet the criteria. This argument can be optional, and if omitted, excel adds up cells in the range being evaluated (range argument).
Usage Notes
- The SUMIF Function can be used to check cells that meet a single condition before summing up. This means that you can only apply one condition with the SUMIF Function. To check for multiple conditions before adding up, you would need to use the SUMIFS Function.
- To be included in the final result, the one condition must be met.
- The criteria can include logical operators such as (>, <, <>, <=, >=). let’s see examples of how to implement logical operators in our criteria in the next section
Defining the Criteria
In the previous section, we noted that logical operators could be combined with text or values to specify the criteria for our SUMIF Function. The table below shows syntax needed for various examples of criteria (including those with logical operators) that can be used in the SUMIF Function.
| Objective | Criteria |
|---|---|
| Cells greater than 50 | “>50” |
| Cells less than 100 | “<100” |
| Cells equal to 75 | 75 or “75” |
| Cells greater than or equal to 200 | “>=200” |
| Cells less than or equal to 150 | “<=150” |
| Cells equal to Blue | “Blue” |
| Cells not equal to Green | “<>”red |
| Cells that are blank | “” |
| Cells that are not blank | “<>” |
| Cells that begin with T | “T*” |
| Cells greater than B5 | “>”&B5 |
| Cells Less than Now | “<“&NOW() |
SUMIF Examples
Basic Examples
Let’s assume there are various numbers within the range B2:B12, we can use the formula below to sum cells greater than 7
In the range A1:A20 we have sales value of different T-shirt designs and in the corresponding range B1:B20, we have the sizes ranging from Small to Large. To sum up the total sales value for the Medium size, we simply use the formula below:
In the first example, we didn’t include the Sum_range, and as stated in the Syntax & Argument section, if this argument is omitted, excel adds up cells in the range being evaluated (range argument). In the second example however, the Sum_range is provided and represented by the range A1:A20.
Moving on to more examples but let us first introduce our data set which would be the basis for the next 3 examples. Our data set contains information of sales by agents working for a company across various cities in the United States.
Coincidentally there are some agents bearing the same name. Using this fortuitous circumstance along with other information contained within the data set, we would illustrate the use of the SUMIF Function for situations such as Summing based on a criteria with multiple occurrences, based on criteria in another cell, not equal to a criteria, and wildcards.
Criteria with multiple occurrences
From the snapshot below, our first hurdle is returning the sum of sales value made by agents named Chapman.
To return the required value for the “Chapman” criteria, we use the following formula:
We have a two for one in this section, so let’s take another example. We need to find the total sales value for all agents based in California. To achieve this, we use the formula below:
Criteria in another cell
For our next trick, we would sum up values in our data set based on criteria that can be found in another cell. Building on the previous example, we need to return the sum of sales value of agents that had at least $5,000 of sales. The thing however is that instead of hardcoding the $5,000 within the formula, we provided it as an input in cell I9 so that we can select from there instead.
Now we move to cell H9 and we input the formula below to sum up sales values of agents that had at least $5,000 of sales.
Not Equal To
This is the last example we would treat with this data set. At this point we are sick of Chapman and we want to return the total sales value excluding those 2 agents having the name Chapman. For this we would need the not equal to logical operator, and this is denoted by the symbol “<>”.
To exclude “Chapman”, we use the formula below:
WildCards
I was thinking of introducing another data set for the remaining sections, but I just remembered that I haven’t treated SUMIF Function using wildcards and this data set is more suited for it. So let’s add some new criteria for our wildcard examples.
As you can see from the above, there are three criteria we need to return values for, the first is to add up sales values of agents having a name starting with “Jus”. If you look closely at the data set, you will notice that we have only one agent with a name starting with “Jus”.
In our example, we have only one occurrence, In larger data sets however you can have multiple occurrences, and the SUMIF Function using wildcards can help to add up the values quickly. To sum sales values for agents having names starting with “Jus”, we use the formula below:
The second criteria is to add up sales value for agents having names ending with “ard”. If we check the data set, we will notice only one agent with name ending with “ard”, and that is Richard. To retrieve the value meeting that criteria, we use the formula below:
Notice that the asterisk used to denote wildcard is at the beginning of the word “ard” rather than at the end as seen in the previous example.
The final illustration using SUMIF with wildcard is to return the sum of total sales value of agents having 5-character names such as “Hobbs”. We have 2 agents with 5-character names, and by using the question mark character (?) to denote wildcard, we are able to add up their sales value using the SUMIF Function.
Dates
We have a new data set for the remaining 2 example categories we would discuss for the SUMIF Function. The data set contains information on a couple of lease contracts with their lease start dates and expected end dates.
Awesome, so we can test for the value of lease contracts ending before a particular date and ending after a particular date. Let’s jump right into it, our two criteria are:
- The total value of lease contracts ending before 31st of December 2027
- The total value of lease contracts ending after 1st of January 2028
To return the value of lease contracts ending in December 2027, we use the formula below:
The second example in this section would show how to use the SUMIF with a greater than or equal to a particular date as criteria. In our example, we need to return the total value of lease contracts ending on or after 1st of January 2028, and to achieve that we use the formula below:
Blank Cells
This is our final example for the SUMIF Function, we are going to add a criteria to return the sales value of blank cells in the lease number column (column C), but we won’t stop at that, we would also show how to return the sales value for non-blank cells in the same column.
To return the sales value of blank cells in column C, we use the formula below:
For our final example, we would return the lease contract value of non-blank cells and to do this, we would use the formula below:
