What function should be used when adding the cells specified by the condition or criteria

The SUMIF function is conditional if the function used to sum the cells based on certain criteria, not the criteria can be a certain text too. For example, we want to sum up a group of cells. If the adjacent cell has a specified text, we can use the function: =SUMIF(Text Range,” Text,” cells range for sum).

Inhaltsverzeichnis Show

• Examples of SUMIF Text in Excel
• Things to Remember
• Recommended Articles

For example,  if we have data on a company’s sales and want to calculate the total sales or need to sum based on names having specific text. In such a scenario, we can use the SUMIF function in Excel to add up values based on criteria.

The Sumif function in excelThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more is used if we want to find out the total of values in a cell range when another set of cell ranges or corresponding array satisfies particular criteria. We can also use the function to add cells that contain specific or partial text.

The general syntax for the SUMIF function is as follows:

In addition, the SUMIF function syntax has the following arguments:

• Range: It is required. It represents the values or range of the cells needed to be tested against the supplied criteria.
• Criteria: It is required. It represents the condition to be checked/tested against each value of the supplied range.
• [sum_range]: It is optional. It represents the values or range of cells required to be added together if the parameter “range” satisfies the given condition/criteria. If this is not provided in the function, then Excel sums the cells specified in the range argument itself.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: Sumif Text in Excel (wallstreetmojo.com)

Examples of SUMIF Text in Excel

Let us understand SUMIF text in Excel with the help of examples.

Example #1

Let us say we have scores of an exam of students of two classes/sections: sections A and B. We wish to find the students’ total scores in section A in an exam.

The scores of students are stored in column C, and the section of students is stored in column B. Then, the following formula tells Excel to return a sum of the total score for all students in section A:

=SUMIF(B2:B11,”A”,C2:C11)

We would specify the function as below:

So, we can see in the above screenshot that a simple SUMIF is sufficient to get results based on a particular text condition. The formula sums up all the scores where the corresponding section is “A.”

So the result is as follows:

The below-highlighted scores will be added to give a total of 379, as their corresponding section is “A.”

Example #2

Now, in the above example, we have one more column that specifies or identifies if the score of a student is “Excellent,” “Good,” “Bad,” or “Average.” So, we wish to find the total scores of students whose score is identified as “Average”:

The scores of students are stored in column C, and the identifier (e.g., “Good,” “Average”) is stored in column D. Then, the following formula tells Excel to return a sum of the total score for all students whose score is identified as “Average”:

=SUMIF(D2:D11,”Average”,C2:C11)

We would specify the function as below:

So, the result is as follows:

So, we can see in the above screenshot that the formula sums up all the scores where the corresponding identifier is: “Average.”

Example #3

We have two columns containing an item and the salesperson required for the item and the total profit in the third column. Now, if we wish to find out the total profit from all the items except hats, then we can use the SUMIF formula with a criterion that finds the total if the cell value is not equal to the given condition:

So, we write the SUMIF condition as below:

=SUMIF(A2:A8,”<>Hat”,C2:C8)

We would specify the function as below:

So, the result is as follows:

So, we can see in the above screenshot that the formula sums up all the profits except the profit corresponding to the item: hat.

It will add the below-highlighted profits to give a total of 352, as their corresponding item is not “Hat”:

Example #4

We have some employees with their team names and salaries. The teams have two categories: “Technical” and “Operations.” Their names begin with ‘Tech’ representing “Technical” and “Options” representing “Operations.” Now, we wish to find out the total salaries of technical teams. In this case, we use the wildcard ‘*’ in the SUMIF function criteria to see if the team name starts/begins with “Tech”:

=SUMIF(B2:B7,”Tech*”,C2:C8)

We would specify the function as below:

So, we can see in the above screenshot that the formula sums all the salaries where the corresponding team names begin with ‘Tech,’ and ‘*’ is used as a wildcard in the text criteria (as above) to accomplish this task.

So, the result is as follows:

Example #5

We have some students with their scores and qualification having three categories: “Graduation of 3 years,” “Graduation of 4 years,” and “Post Graduation.” They are named: “Grad3,” “Grad4,” and “PostGrad,” respectively. Now, we wish to find out the total scores of “Grad3” students. In this case, we use the wildcard ‘*’ as follows:

=SUMIF(B2:B8,”G*3″,C2:C8)

We would specify the function as below:

So, we can see that we can use ‘*’ to test a sequence of characters: “G*3” in the above formula tests or matches all cells containing a string that begins with “G” and ends with ‘3’. So, the scores where the corresponding qualification is “Graduation3” are added to a total of 135.

So, the result is as follows:

Things to Remember

• The SUMIF function is a built-in function in Excel categorized as a Mathematical/Trigonometric function.
• The SUMIF text in Excel is useful in cases where we wish to sum numbers in a range of cells based on text criteria.
• The SUMIF function is not case-sensitive.

We see that the text criteria: “Average” and “average” will be treated or evaluated as the same.

• The parameter as “criteria” to the SUMIF function can be either a numeric value (integer, decimal, logical value, date, or time), a text string, or even an expression.
• If the parameter provided as “criteria” to the SUMIF function is a text string or an expression, it must be enclosed in double quotes.
• The wildcards used in text criteria are: “?” to match a single character and ‘*’ to match the sequence of characters.
• If we need to find an actual or literal question mark or asterisk in the supplied range, we can use a tilde (~) in front of the question mark or asterisk ( ~*, ~?).
• The logical operators in excelLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more that we can use in expression criteria are:
  • Less than operator: “<.”
  • Greater than operator: “>.”
  • Less than or Equal to the operator: “<=.”
  • Greater than or Equal to the operator: “>=.”
  • Equal to the operator: “=.”
  • Not Equal to the operator: “<>.”
  • Concatenate operator: “&.”
• If the parameter provided as “criteria” to the SUMIF function is a text string more than 255 characters long, then the function returns the #VALUE! Error.
• If we wish to find the sum of values of a range based on multiple criteria, then the SUMIFS function is used.

Recommended Articles

This article is a guide to SUMIF Text in Excel. Here, we discuss examples of how to SUMIF cells that contain text in another cell, along with a downloadable Excel template. You may also look at these useful functions in Excel: –

• Excel Extract Number from StringExcel has Left, MID, and Right functions for extracting a part of a text value or string value. Other supporting functions, such as "Find and LEN," can be used to make the formula more dynamic.read more
• SUMIF With VLOOKUPSUMIF is used to sum cells based on some condition, which takes arguments of range, criteria, or condition, and cells to sum. When there is a large amount of data available in multiple columns, we can use VLOOKUP as the criteria.read more
• SUMIF Between Two DatesWhen we wish to work with data that has serial numbers with different dates and the condition to sum the values is based between two dates, we use Sumif between two dates. read more
• AverageIF in ExcelAverageIF in excel calculates the average of the numbers just like the average function in excel. However, the difference is that AverageIF is a conditional function and calculates the average only when the criteria are met. It takes three required arguments, range, criteria and average range.read more