SUMIF Text In Excel

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).

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 is conditional, meaning it can sum cells based on specific criteria, such as text. For instance, if a cell has a specified text, the function can be used to sum up a group of cells based on that text.
• The SUMIF function is an Excel built-in mathematical/trigonometric function.
• In Excel, the SUMIF text is handy when we want to sum numbers in a range of cells depending on text criteria. The SUMIF function does not care about the case.

The Sumif function in excel 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.
• : 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.

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:

• 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.
• 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.

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 String
• SUMIF With VLOOKUP
• SUMIF Between Two Dates
• AverageIF in Excel