The SUMIF Function in Excel is used to sum the the range values in a range that meet a specified criteria. For example, having a spreadsheet with information about the money spent in certain month, the user can apply this function to sum only the values that correspond to a specific month.
As a member, you'll also get unlimited access to over 84,000 lessons in math, English, science, history, and more. Plus, get practice tests, quizzes, and personalized coaching to help you succeed.
Get unlimited access to over 84,000 lessons.
Try it risk-freeAlready registered? Log in here for access
The SUMIF Function follows this structure:
{eq}\text {=SUMIF(Range, Criteria, Sum_Range)} {/eq}
Basically, what the formula shows is this :
=SUMIF (In which range of cells is Excel going to find the data that meets the criteria?, What criteria must the data comply with?,What values is Excel going to SUMIF the condition is true?)
If the first cell in the range meets the proposed condition, Excel will SUM the corresponding value. If the first cell in the range does not meet the proposed condition, Excel doesn't SUM anything. The function then continues to the next cell, and so on and so forth until it reaches the last cell of the range. This means that the result of the formula is the SUM of all the corresponding values that met the criteria.
This function includes three arguments: the range, the criteria the cells need to fulfill, and the range of values to SUM if the criteria is fulfilled.
The range is the list of cells that the function is going to evaluate and compare with the given criteria. The range can include rows, columns, or a matrix of rows and columns.
The criteria is the condition that the cell in the selected range must follow to be considered TRUE, and to add +1 to the counter. This condition can be expressed through logical operators or wildcards.
| Logical Operators | Condition | TRUE Condition Examples |
| = | Equal to | 3=3 (three is equal to three) |
| < | Less than | 3<4 (three is less than 4) |
| > | Greater than | 3>2 (three is greater than 2) |
| <> | Not equal to | 3<>2 (three is not equal to 2) |
| <= | Less than or equal to | 3<=3 (three is less than or equal to three) |
| >= | Greather than or equal to | 3>=3 (three is greather than or equal to three) |
Another feature that Excel supports is the use of wildcards to find a specific condition within the cells of the spreadsheet.
| Wildcard | Condition | Examples |
| * | Counts the number of cells that contain one or various alphanumeric characters | "*Green*" (Is TRUE for cells that contain the word "Green"), "1*4" (Is True for cells that contain the 1 and 4 numbers), "*s"(Is true for cells that cointain words ending with S), "s*"(Is true for cells that Start with S) |
| ? | Counts the number of cells that contain a unique alphanumeric character. It doesn't matter where it is located in the cell | "Green?"(Is true for cells that contain the unique word Green), "S?T" (Is true for cells that Start with S and end with T like "SAT" or "SIT", but false for "SUM") |
| ~ | Is used when the characters in cells you want to compare contain the characters "*" or "?" | "*~?" (Is true when the cells containt text that ends in "?"), "~*" (Is true if the cell cointains the exact character "*") |
These are the cells that are going to be added. If it is specified in the arguments of the formula, the function will add the values in the range that meet the criteria. If the Sum range is not specified in the formula, then Excel will add all the values in the range. The Sum Range must be the same size of the Range. If the Range covers 10 cells, the sum range needs to cover also a range of 10 cells.
Sometimes the user has values in a range of cells, but only wants to sum some of them, not all. This values to sum need to coincide with an specific condition. This condition can be a text condition, numeric condition or a date. The general process that goes into applying the function formula is the same for all kinds of data:
The SUMIF Function is different to the SUMIFS Function because the first one allows to compare the data in the range with only one criteria, and the second one allows to compare with more than one criteria.
In many cases, the user will need to sum values in a range that meet with a numeric condition. Consider the following situation: One store is awarding bonuses of $ 5,000 to salespeople who sell 5 or more cars and $ 10,000 to those who sell 10 or more. The manager has an Excel spreadsheet with the information about how many cars the salespeople sold, and how much in bonuses each one will receive. He wants to know how much money in $10,000 bonuses he needs to ask for, because these are the first bonuses to be paid. He applies the SUMIF Function to find out that data.
 |
The Range of cells where the information about how many cars the salespeople sold is between the cells E3:E11, the logical operator that expresses what the user needs to find is ">=". The sum range is between the cells F3:F11 and it is the same size as the range.
When using COUNTIF for numeric data, the syntax changes -- first, the logical operator must be placed between quotation marks, and second, the user must write the ampersand symbol (&) to make a reference to a cell that cointains a numeric value. It is important to write the quotation marks on both sides of the logical operator, so that Excel doesn't take the ampersand symbol as part of the actual criteria. Taking into account all of this information, the resultant formula is:
=SUMIF(E3:E11,">="&10, F3:F11)
Here is what this formula expresses: Here is what this formula expresses: When the values in the range E3:E11 contain are greater or equal to 10, add the corresponding value in the range F3:F121and give me the total sum". So the result is $30,000.
Text information is also commonly used in Excel spreadsheets. Logical operators and wildcards can apply to functions involving text data as well as they did with numerical criteria. This will help the user to determine specific information about the database. The difference lies in that while working with text data, comparisons between cells with the logical operators greater than, less than, greater than or equal to, and less than or equal to, are not allowed, because the text characters do not involve numeric quantities. It is possible, however, to compare whether a cell is equal or not equal to a specific text value.
Let's see this example: A man is reviewing the expenses he made for the week, so he creates a spreadsheet where he categorizes each of the expenses he made and includes the amount. One of the categories is Transportation, so he wants to find out how much he spent in the week. He uses the SUMIF Function in Excel.
 |
The range of the data to compare with the criteria is between the cells E4:E12. The criteria is an "equal to" condition because it will add all the values in the sum range that correspond to the cells in the range with the text "TRANSPORTATION". The sum range is F4:F12 because is the range of cells where the amount of each expense is. So the formula is:
=SUMIF(E4:E12,"TRANSPORTATION",F4:F12)
Here is what this formula expresses: When the values in the range E4:E12 contain the word "TRANSPORTATION", add the corresponding value in the range F4:F12 and give me the total sum". So the result is $213 spent in TRANSPORTATION.
Comparisons between cells and conditions also apply to cells that contain dates. With this kind of data, the user can use all of the logical operators (unlike with text data) and wildcards.
Consider this case: a soccer team has a table with information about the attendance in the games played in their stadium in 2021. After March, government allowed teams to increase the attendance to 100% of the capacity of the stadium (because of COVID19 restrictions).
 |
The range of the data to compare with the criteria is between the cells D7:D22. The criteria is ">31/03/2021" because we want that Excel add the values in the sum range that in the range are after March. The sum range is C7:C22 because is the range of cells where the information about attendance is. So the formula is:
=SUMIF(D7:D22,">31/03/2021",C7:C22)
Here is what this formula expresses: When the dates in the range D7:D22 are after March, add the corresponding value in the range C7:C22 and give me the total sum". So the result is 807694 people who attended the stadium.
Here is a particular case: A manteinance company was assigned to take care of 4 houses in a private neighborhood. Along 2021 they have executed work of plumbing, paint, cleaning and eletricity. The owner of the neighborhood is asking for information about: expenses made in the house number 4, expenses made before June and expenses made in the cleaning item.
 |
So the manager of the company organizes the information in a spreadsheet in Excel. He identifies that applying the SUMIF FUNCTION he can get all the information the owner requires:
=SUMIF(D4:D14,"="&4,G4:G14)
The logical operator is "=" because he wants all the data that is equal to house number 4. So the result of the formula is: $557 spent on manteinance of house 4.
=SUMIF(F4:F14,"<1/6/2021",G4:G14)
The logical operator is "<" because he wants all the data that is before june or what is the same less than 01/06/2021. So the result of the formula is $3397 spent before june.
=SUMIF(E4:E14,"CLEANING",G4:G14)
The logical condition is an equal to operator, and because is text information it is writen betweeen quotation marks. The result is $1665 spent in the CLEANING item.
The SUMIF Function can be applied to a range and if a specified criteria is fulfilled, the values in a different range are added, giving as result the total sum. The information in these cells can be a number, a text or a date. Logical operators and wildcards are used to define the criteria that must match with the cells in the selected range. If the cell meets the criteria, the value in the sum range is added. It continues onto the next cell until it has searched through all the selected cells. This function is useful for making sums of a portion the total information depending on a condition we propose.
To unlock this lesson you must be a Study.com Member.
Create your account
The user provides a range of values to compare with a specified criteria. He also provides a sum range (Values that are going to be added if the condition is true. In the first cell of the range if the condition is true, the corresponding value is added, if it is FALSE it adds nothing. And this continues through all the cells in the range.
The SUMIFS function in Excel is different to the SUMIF function because it compares the values in the range with two or more criterias. The SUMIF function only allows one criteria to compare with.
Already a member? Log In
BackAlready registered? Log in here for access
