SUMIF and SUMIF SET: Adding values

Key information

ADD IF allows us to add values from a selection of data (“range”) based on a condition (“criterion”) defined by us. Excel will evaluate the condition in each of the cells that we indicate and will add the values only if they meet the condition. Then he will give us the total sum of those who complied.

You can complement your learning with the following video:

The basics

• Purpose: ADD IF It allows us to add values in a range of data, according to a criterion defined by us. 
• Characteristics: It is the union of two other Excel functions, SUM and YEAH, in the same package. Cells selected for the function can go in any direction.
• Syntax:

=SUMIF(range; criterion; [sum_range])

• Arguments:

range = The selection of cells where the condition will be evaluated (criterion). In case it is omitted sum_range It will also be the selection that contains the values to be added.

criterion = The condition according to which it will be added.

sum_range = Optional. These are the cells that contain the values to be added, depending on whether the condition is met in the range. If it is omitted, the values in the range.

<!–[if lte IE 8]>

<![endif]–> hbspt.cta.load(21347035, '86268834-02d4-4a46-bf2b-876eef55b33c', {“useNewLoader”:”true”,”region”:”na1″});

Step 1: Selecting a data range

When using this Excel function we have to select the data on which we want to evaluate the condition that we define. We call this “range” and it can be selected in any direction.

When defining the range for the SUM IF function we must see if the data we want to add is the same where we want to evaluate the condition:

• If the data is the same, we only include a range and a condition in the function.
• If the data we want to add is not the same as where we evaluated the condition, we must indicate to the function the range where they are located (the sum_range argument).

We will now see examples of how it is done in each case:

Case 1: Range for the condition is equal to the range of values to be added

We will now see the case in which the range where the condition is evaluated is the same one that contains the values to be added. In this situation it is not necessary to indicate a sum_range to the SUM IF function.

In the following example, we want to add all the numbers that are greater than 1. Since the values range from B3 to F3, we select this line as our range:

Thus the range is defined as:

• range = B3:F3

We can see that it is not necessary to define another range for the values to be added, since it is on the same numbers that it is evaluated if they are greater than 1, and then add them. Thus, only 5 and 3 meet the condition, so the result is 8:

Case 2: Range for the condition is different from the range of values to be added

We will now see the case in which the range where the condition is evaluated is different from the one that contains the values to be added. In this situation it is necessary to indicate a sum_range to the function, that is, the range where the values to be added are found.

Going back to the previous example, we want the function to add all the numbers that have an X over them:

Since now the condition is whether there is an On the other hand, the values we want to sum are in B3:F3, so we select this line as our sum_range:

Thus the range and range_sum are defined as:

• range = B2:F2
• sum_range = B3:F3

Step 2: Defining a criterion

When applying the Excel function SUM YES we must define a criterion, that is, the condition on which Excel will choose cells to add. This condition can take different forms depending on what we want to look for.

ADD IF according to a numerical criterion

We can use the Excel function SUM IF to add the cells that contain the numerical criteria that we give it. Taking the example of the following image, we want to see the sum of salaries that are equal to $1,600,000:

Since we are looking at salaries, our range becomes the “Salary” column, which goes from cell F3 to F7. In this case, the condition is a salary equal to $1,600,000, so the ADD IF function includes 1,600,000 as a criterion. As we see, since the range where the condition is evaluated is the same where the values are added, it is not necessary to include a sum_range. So:

• range = F3:F7 (Data column “Salary”)
• criterion = 1600000 (The person has a salary of $1,600,000)

That is, our SUM IF function corresponds to:

• =SUMIF(F3:F7,1600000)

This results in $3,200,000, since only 2 people meet the condition:

In turn, the condition of the Excel function SUM IF can be the value of another cell. In the image we see an example of how to enter this:

Now the condition is given by cell H4, which contains the salary $1,600,000, which will obtain the same result.

ADD IF according to a criterion in words

We can also use the Excel function SUM IF to add according to a criterion in words. Now when writing the condition we must worry that the word is enclosed in quotes (“”), since with this we tell the ADD IF function that it is a text. In this case we will always have to include a range and a sum_range as parameters of the function, since we cannot add cells that contain text, so values will be in the sum_range.

In the following example we want to add the salary of all the people who are analysts:

We can see that now our range is the “Position” column, while our sum_range is the “Salary” column. The result that SUMAR SI will give us is the same as the previous example, $3,200,000, since the same 2 people meet the condition of being analysts.

As with numbers, it is possible to remove the condition from another cell, where it is not necessary to add quotes because the ADD IF function will recognize that the original cell is text:

The condition in this case comes from cell I4, which says Analyst, so ADD IF will add the salaries of the 2 people who are analysts and will give us $3,200,000 as a result.

Ninja Tip: You can combine the SUM IF function with other conditional functions to achieve results that depend on more complex conditions. An example is to use it in conjunction with the function COUNT YES to create the average of the observations that meet a certain condition.

Logical operators and ADD IF

We can combine the Excel function SUM IF with logical operators (>,<,<>,=) to make more complex conditions. These operators are:

• > Greater than
• < Less than  
• <> Different from
• = Same
• >= Greater than or equal to
• <= Less than or equal to

Now we want the ADD IF function to add the salaries of all those people who are over 30 years old. For this we use > (greater than) and 30 as parts of our condition. But since > corresponds to text, our entire condition must be enclosed in quotes:

This function will give us $8,900,000 as a result, since everyone except Juan Bilbao is over 30, so those 4 salaries are added:

Suppose that now we want to add the salary of all those people who do not have the position of analyst. We use the SUM IF function including the operator <> (not equal to) and the word analyst.

Our range is now the “Position” column while the sum_range is still the “Salary” column. This gives us the following result:

Ninja Tip: This function does not recognize capital letters, so using “Analyst”, “analyst” or “AnAlIStA” as a condition gives the same result.

We can also use logical operators and conditions that come from a cell, but using the “&” character. In this case, it is the logical operator that is enclosed in quotes, but “&” is not. The following image shows the example we used previously about people over 30 years old:

Ninja Tip: You can use the SUMIF function including other functions as conditions to achieve better results. An example is to use the Excel function AVERAGE and “>” to add those salaries that are above the average.

<!–[if lte IE 8]>

<![endif]–> hbspt.cta.load(21347035, '86268834-02d4-4a46-bf2b-876eef55b33c', {“useNewLoader”:”true”,”region”:”na1″});

Dates

A common variation when using the Excel SUM IF function is to work with dates in the condition we use. For this we must be careful that the data we refer to is in Excel date format. In the following example we want to add all the sales that were after May 18, 2020, so in the condition we use the logical operator “>=” and the date 5/18/2020 (May 18, 2020). This data comes from another cell, since it is necessary to tell Excel that it corresponds to a date:

We can also do the same by including the function DATE as part of the condition and using the year, month and day as arguments:

In both cases we will obtain the same result:

This is because only 2 dates meet the condition.

Ninja Tip: Certain countries work with date order differently, for example by putting the month before the day in the expression. Make sure you have everything in the format you want. One way to confirm that we are doing it right is to use Excel functions DAY, MONTH and YEAR; and see what they give us when using them on the data we are analyzing.

Wild cards

There are certain characters that can be used in the SUMIF function, known as “wildcards”, which allow special actions to be performed on the conditions.

Asterisk

Depending on where the asterisk is located in the condition (*), it can tell the Excel function SUM IF different things:

• If it goes at the beginning of the condition (example: “*dro”) it indicates that it may come something before what is written. In the example, the SUM IF function will recognize “Pedro” and “Alejandro” as cells that meet the condition.
• If it goes to the end of the condition, indicate that it can come somewhat after what is written. If we now write the condition as “Pe*”, SUMAR SI will recognize “Pedro”, but not “Alejandro”.
• It can also go to the beginning and end of the condition, indicating that something can come before and after what is written. If we write the condition as “*dro*”, SUMAR SI will recognize “Pedro Pablo” as a cell that meets the condition.

In the following example we want to add the salaries of the people whose names begin with the letter “C”, so we write “C*” as a condition, since this way we tell Excel that the condition is that it starts with C, but that Anything can come later:

This will give us $5,000,000 as a result, since only Carolina and Christine meet the condition:

Question mark

The question mark (?) indicates to the ADD IF function that any character can go in the place where we place the sign. In the following example we want to add the salaries of people named Juan or Joan, who only differ in the second character, which we replace with “?”, to tell Excel that any character is allowed in that place:

This will give us the result $3,100,000, since Excel recognizes the names Juan and Joan as meeting the condition:

We must remember that the “?” replaces only one character, so in the previous example the function would not have included a person named Julian. If we wanted to add Julián's salary, the condition would be “J???án”.

Ninja Tip: If we want to include a question mark as part of the condition without it fulfilling its function of replacing a character, we must first write a “~” (~?).

SUM IF SET: Adding according to several criteria

• Purpose: ADD IF SET It is similar to ADD IF, but allows you to include different conditions to obtain a single result.
• Characteristics: Unlike SUM IF, in this case the sum_range is mandatory and goes at the beginning. The first range and criteria are also required, but you can have up to 126 optional extra range/criteria pairs. Extra ranks cannot exceed the size of the first rank.
• Syntax:

=SUM.IF.SET(sum_range; criteria_range1; criterion1, [criteria_range2; criterion2]; …)

• Arguments:

sum_range = Range containing the values to be added

criteria_range1 = Range where criterion1 is evaluated

criterion1 = Condition that is evaluated in criteria_range1

criteria_range2, criterion2… = Extra pairs of ranges and criteria

The Excel function SUM IF SET allows us to add data in Excel that meets 2 or more conditions. In the following example we want to add the salaries of people who are over 30 years old and are analysts. For this, our first condition will be that the person is over 30 years old and their rank will be the “Age” column. Our second condition will be that the person is an analyst and their rank will be the “Position” column. Along with this, our sum_range will still be the salaries column:

The elements that our function contains are:

• sum_range = F3:F7 (Column “Salary”)
• criteria_range1 = D3:D7 (Column “Age”)
• criterion1 = “>30” (Be over 30)
• criteria_range2 = E3:E7 (Column “Position”)
• criterion2 = Analyst (Be an analyst)

That is, our function corresponds to:

=SUMIF SET(F3:F7; D3:D7;”>30”; E3:E7; “Analyst”)

The result we obtain is $1,600,000, since only Magdalena Tapia meets both conditions, so the function only gives us her salary:

Let's deepen the knowledge...

How does SUMIF SET work?

The function SUM IF SET Excel is a very useful tool because it provides the value that adds up the values that meet certain criteria. Additionally, through this formula, we can perform partial searches using wildcards. In this article we will teach you how to handle it perfectly. 

Key information:

The SUMIF.SET function allows you to add cells that meet multiple criteria, that is, this formula searches a range of cells for values that meet a condition and then adds the values found. 

Ninja Tip:  Excel also offers the SUMIF function. This function has the same functionality as SUMIF SET with the difference that SUMIF allows adding cells that meet a single criterion, while SUMIF SET offers adding cells with up to 127 criteria. If you want to learn more about this feature, we recommend visiting ADD IF.

The basics

• Purpose: The SUMIF SET function allows you to add cells that match multiple specified criteria.     
• Characteristics: This function allows adding values that meet different criteria, such as numbers, texts, dates, and also supports different logical operators, such as >, < or =. On the other hand, it also allows sums to be made with partial criteria.  
• Syntax: =SUM.IF.SET(sum_range; criteria_range1; criterion1; …)
• Arguments:
  • sum_range: Range of cells that contains the values to be added.
  • criterion_range1: Range of cells that will be evaluated by the criterion1.
  • criterion1: Criterion that the cells of the criterion_range1.

<!–[if lte IE 8]>

<![endif]–> hbspt.cta.load(21347035, '86268834-02d4-4a46-bf2b-876eef55b33c', {“useNewLoader”:”true”,”region”:”na1″});

Using the SUMIF SET function

The SUMIF SET function in Excel allows you to add cells that meet certain criteria.  

To better understand the logic of SUMIF SET, let's look at a simple example. The following table contains information about a t-shirt factory, where it shows us the code, size, color and stock of the t-shirts.

We want to know the stock of t-shirts that have the code “1”, that are in size “M” and that are in “Green” color.

In simple words, the SUMIF SET function will search the columns for the determined data and add the stock of those t-shirts that meet all the requirements.

Then, Excel will look in the code column for those that match a “1”, then in the size column it will look for those that match “M” and, finally, in the color column, it will look for those that be “Green”. 

After finding the matches, Excel will add the stock of those t-shirts that meet the 3 criteria. We can see that two shirts meet all the criteria, therefore, Excel will add these two stocks (10 and 20).

Then, the elements of the SUMIF SET function will be:

• sum_range: E3:E9 
• criterion_range1: B3:B9 
• criterion1: 1
• criterion_range2: C3:C9
• criterion2: “M”
• criterion_range3: D3:D9
• criterion3: "Green"

Tip Ninja: Remember that, if the criterion is a text value, we must write it in quotes, otherwise Excel will not recognize it. 

That is, the SUMIF SET function is:

Therefore, the result we will obtain will be:

That is, there is a stock of 30 t-shirts that have code 1, size M and are green.

SUMIF SET with more than one logical operator

The SUMIF SET function allows you to use more than one logical operator within the function, but it does not allow you to use two logical operators within the same criterion.

Let's look at an example, we want to know the total salaries that a company gives to female employees, whose age is in the range 40-49 years and who belong to the production area.

Since we are looking for the age range 40-49 years, it means that we want ages that are greater than or equal to 40 and less than 50, that is, the criterion for age is as follows: 40 ≤ Age < 50.

Tip Ninja: The SUMIF SET function does not allow you to make a criterion with a double logical operator, that is, we cannot write “≥40<50”, due to this, we recommend separating the criterion into two parts, one containing “≥40” and another “<50”. Also, remember that every time we use a logical operator, we must enclose the criterion in quotes.

The elements of the function are:

• sum_range: F3:F11
• criterion_range1: C3:C11 
• criterion1: "Women"
• criterion_range2: D3:D11
• criterion2: “<50”
• criterion_range3: D3:D11
• criterion3: “>=40”
• criterion_range4: E3:E11
• criterion4: "Production"

That is, the SUMIF SET function is:

The result we will obtain will be:

We see that the total salaries that the company must pay to female employees, aged between 40-49 years who belong to the production area, is $1,060,000.

SUM IF SET with logical operator “<>”

The SUMIF SET function allows you to use any type of logical operator. In this case, we will show you how to use the “exclude” logical operator, since it is a very useful but little-known tool.

Let's look at an example, we want to know the total salaries that a company gives to male employees who do not belong to the finance area.

In this case, since we are looking for areas that are not finances, using the <> operator we can exclude the finances area. For this, the criteria used will be as follows: “<>Finances”

The elements of the function are:

• sum_range: F3:F11
• criterion_range1: C3:C11 
• criterion1: "Man"
• criterion_range2: E3:E11
• criterion2: “<>Finance”

That is, the SUMIF SET function is:

The result we will obtain will be:

We see that the total salaries that the company must pay to male employees who do not belong to the finance area is $2,270,000.

SUM IF SET with wildcard

The SUMIF SET function allows you to use partial criteria, this means that we can perform searches with certain specific values. 

For this, we must use wildcards. A wildcard is a special character that allows similar matches to be made. Excel interprets the “?” signs as wildcards. and "*". The wildcard “?” represents any character, however, “*” represents any sequence of characters. 

For example, we want to sum all the salaries of male employees whose name begins with the letter T, that is, we are interested in names that begin with the letter “T” regardless of the characters that continue.

As all names have different amounts of characters, we cannot use the wildcard “?”, but we can use “*” since this wildcard searches for any combination of characters, that is, the criterion will be of the form: “=T*”.

The elements of the function are:

• sum_range: F3:F11
• criterion_range1: C3:C11 
• criterion1: "Man"
• criterion_range2: B3:B11
• criterion2: “=T*”

That is, the SUMIF SET function is:

The result we will obtain will be:

We see that the total salaries that the company must pay to male employees whose name begins with the letter “T” is $1,070,000.

Common mistakes

* 0 zero)

The SUMIF SET function can return a zero, different from the expected value, when some criterion that needs to be enclosed in quotes is not.

* #VALUE!

This error occurs when the criterion_range does not contain the same number of rows as the sum_range of the SUMIF SET function, we recommend that you review the range selected for each criterion. 

Ninja Tip:  You can use the IF.ERROR or IF.ND functions to handle errors.  

Tip Ninja: When you have more than 127 criteria, which is the maximum that the SUMIF SET function supports, we recommend using the CONCATENATE to have a smaller number of characters.

<!–[if lte IE 8]>

<![endif]–> hbspt.cta.load(21347035, '86268834-02d4-4a46-bf2b-876eef55b33c', {“useNewLoader”:”true”,”region”:”na1″});

Frequent questions