## Table of Contents

- What is a SUMIF Function in Excel?
- Syntax of SUMIF Function
- Uses of the SUMIF Function in Excel
- Examples
- Lesson Summary

What is a SUMIF Function and how it is used in Excel? Learn the formula and syntax of the SUMIF function. Understand how to use the SUMIF function in Excel.
Updated: 11/23/2021

- What is a SUMIF Function in Excel?
- Syntax of SUMIF Function
- Uses of the SUMIF Function in Excel
- Examples
- Lesson Summary

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.

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:

- For every Excel function, the user must write the "=" symbol to start introducing the formula
- Then the user must write the name of the function -- in this case: =SUMIF
- The function's arguments are written between parenthesis: =SUMIF(Range, Criteria,Sum_range)
- To select the range, the user can either type the start and end cells directly into the formula bar, or they can click the first cell and drag their cursor to the end of the range to select the whole thing.
- The criteria is specific to the situation we have and the information we want to extract, but it must always be written between quotation marks, like so: " ". Further sections of the lesson will provide more information about how to format the criteria.
- The sum range must be selected the same way as the range, and it has to be the same size of that range.

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:

- To find the expenses made in the House number 4 he uses SUMIF Function for numeric criteria. The range of the house number is between the cells D4:D14 and the sum range of the amount spent is between the cells 64:G14. So he applies the SUMIF Formula:

**=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.

- To find the expenses made before june he uses SUMIF Function for date criteria. The range of date of the expenses is between the cells F4:F14 and the sum range of the amount spent is between the cells 64:G14. So he applies the SUMIF Formula:

**=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.

- To find the expenses made in the CLEANING item, he uses SUMIF Function for text criteria. The range for the items is E4:E14 and the sum range of the amount spent is between the cells 64:G14. So he applies the SUMIF Formula:

**=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

Frequently Asked Questions

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.

Are you a student or a teacher?

Already a member? Log In

BackWhat teachers are saying about Study.com

Already registered? Log in here for access

Related Study Materials

Browse by subject