**Excel Sumif Text Function (Table of Contents)**

## Excel Sumif Text

Sumif function 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:

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.

### 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: 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:

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,

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

4.9 (1,353 ratings)

#### 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 total of scores of students whose score is identified as ‘Average’:

The scores of students are stored in column C and the identifier (eg: ‘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:

So the result is as follows,

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 given condition:

So, we write the SUMIF condition as below:

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

The function would be specified as below:

So the result is as follows,

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 #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 ‘Oprtns’ 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:

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

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:

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,

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

We see that the text criteria: ‘Average’ and ‘average’ will be treated or evaluated as 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 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 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 ‘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 downloadable excel template. You may also look at these useful functions in excel –

- How to Create Family Tree in Excel Template?
- Create a Word Cloud in Excel
- Excel Extract Number from String
- SUMIF With VLOOKUP
- SUMIF Between Two Dates
- AverageIF in Excel
- COUNTIF with Multiple Criteria

- 35+ Courses
- 120+ Hours of Videos
- Full Lifetime Access
- Certificate of Completion

- Basic Excel Training
- Advanced Excel Training
- Basic & Advanced VBA Course
- Excel Dashboard Course
- Data Analysis in Excel
- Create VBA Applications