Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create Column Chart in Excel
  • How to sum a total in multiple Excel tables
  • Example of COUNTIFS with variable table column in Excel
  • Excel Pie Chart
  • Excel Bar Chart

References

  • How to use Excel ROWS Function
  • Excel Advanced Lookup using Index and Match Functions
  • Vlookup Examples in Excel
  • How to get last column number in range in Excel
  • Create hyperlink with VLOOKUP in Excel

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation unique values only
  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation must not contain

Sum if cells contain specific text in Excel

by

This tutorial shows how to Sum if cells contain specific text in Excel using the example below;

Formula

=SUMIF(range,"*text*",sum_range)

Explanation

To sum if cells contain specific text, you can use the SUMIF function with a wildcard.

In the example shown, cell G6 contains this formula:

=SUMIF(C5:C11,"*t-shirt*",D5:D11)

This formula sums the amounts in column D when a value in column C contains “t-shirt”. Note that SUMIF is not case-sensitive.

Worked Example:   Sum if date is greater than in Excel

How the formula works

The SUMIF function supports wildcards. An asterisk (*) means “one or more characters”, while a question mark (?) means “any one character”.

Worked Example:   Sum if greater than in Excel

These wildcards allow you to create criteria such as “begins with”, “ends with”, “contains 3 characters” and so on.

To match all items that contain “t-shirt”, the criteria is “*t-shirt*”. Note that you must enclose literal text and the wildcard in double quotes (“”).

Worked Example:   Sum if cells are equal to in Excel

Alternative with SUMIFS

You can also use the SUMIFS function. SUMIFS can handle multiple criteria, and the order of the arguments is different from SUMIF. The equivalent SUMIFS formula is:

=SUMIFS(D5:D11,C5:C11,"*t-shirt*")

Notice that the sum range always comes first in the SUMIFS function.

Post navigation

Previous Post:

44 Practical Excel IF function Examples

Next Post:

Large with criteria in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Check multiple cells are equal in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function

Date Time

  • How to calculate workdays per month in Excel
  • Get work hours between dates custom schedule in Excel
  • Convert Excel time to Unix time in Excel
  • Get days before a date in Excel
  • How to get workdays between dates in Excel

Grouping

  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Group numbers with VLOOKUP in Excel
  • Group times into 3 hour buckets in Excel
  • How to randomly assign data to groups in Excel

General

  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • Subtotal invoices by age in Excel
  • How to password protect excel sheet?
  • Find, Select, Replace and Go To Special in Excel
  • Count cells that do not contain errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning