Sumif Text in Excel

SUMIF function is conditional if function which is used to sum the cells based on certain criteria, not the criteria can be a certain text too, for example we want to sum of group of cells if the cell adjacent to them have a specified text in them then we use function as follows =SUMIF(Text Range,” Text”, cells range for sum).

Excel Sumif Text

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 range or corresponding array satisfies particular criteria. The function can also be used to add cells that contain specific or partial text.

General Syntax for SUMIF function is as follows:

Sumif Text Function

The SUMIF function syntax has the following arguments:

  • Range: Required represents the values or range of cells that are required to be tested against the supplied criteria.
  • Criteria: Required, represents the condition to be checked/tested against each value of the supplied range.
  • [sum_range]: Optional, represents the values or range of cells that are 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.

Sumif Text in Excel

Examples of SUMIF Text in Excel

Let us understand Sumif Text in excel with the help of examples.

You can download this Sumif Text Excel Template here – Sumif Text Excel Template

Example #1

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

The scores of students are stored in the column: C, and the section of students are stored in a 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)

The function would be specified as below:

Sumif Text Example -1.1.1

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 all the scores where the corresponding section is: ‘A.’

So the result is as follows,

Example -1.1.2

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

Sumif Text Example -1.1.3

Example #2

Now, let’s say 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,’ and we wish to find the total of 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)

The function would be specified as below:

Example -2.1.1

So the result is as follows,

Sumif Text Example -2.1.2

So we can see in the above screenshot that the formula sums all the scores where the corresponding identifier is: ‘Average.’

Example #3

Let’s say 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 which finds 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)

The function would be specified as below:

Example -3.1.1

So the result is as follows,

Sumif Text Example -3.2

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

The below-highlighted profits will be added to give a total of 352, as their corresponding item is not ‘Hat’:

Example -3.3

Example #4

Let’s say we have some employees with their team names and salaries. The teams are of two categories: ‘Technical,’ or ‘Operations,’ and their names begin with ‘Tech’ representing ‘Technical’ and ‘Options’ representing ‘Operations.’ Now, we wish to find out the total of 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)

The function would be specified as below:

Sumif Text Example- 4.1.1

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- 4.1.2

Example #5

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

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

The function would be specified as below:

Sumif Text Example -5.1.1

So we can see that ‘*’ can be used 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 give a total of 135.

So the result is as follows,

Example- 5.1.2

Things to Remember

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

Sumif Text Example

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

  • The parameter provided as ‘criteria’ to the SUMIF function can be either a numeric value (integer, decimal, logical value, date, or time), or a text string, or even an expression.
  • If the parameter provided as ‘criteria’ to the SUMIF function is a text string or an expression, then it must be enclosed in double-quotes.
  • The wildcards that can be used in text criteria are: ‘?’ to match a single character and ‘*’ to match the sequence of characters.
  • In case we need to find an actual or literal question mark or asterisk in the supplied range, then we use a tilde (~) in front of the question mark or asterisk ( ~*, ~?).
  • The logical operators in excel that can be used 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 that is more than 255 characters long, then the function returns ‘#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 has been 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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>