Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count rows with at least n matching values
  • How to get first row number in range in Excel
  • How to use Excel MATCH Function
  • How to use Excel LOOKUP Function
  • Two-column Lookup in Excel

Data Analysis Examples

  • Conditional Formatting Rules in Excel
  • Randomize/ Shuffle List in Excel
  • Data Series in Excel
  • How to Use Solver Tool in Excel
  • How To Load Analysis ToolPak in Excel

Data Validation Examples

  • Data validation must not exist in list
  • Excel Data validation whole percentage only
  • Excel Data validation with conditional list
  • Excel Data validation date in specific year
  • Excel Data validation only dates between

Sum if cell contains text in another cell in Excel

by

This tutorial shows how to Sum if cell contains text in another cell in Excel using the example below;

Formula

=SUMIF(range,"*"&A1&"*",sum_range)

Explanation

To sum if cells contain specific text in another cell, you can use the SUMIF function with a wildcard and concatenation. In the example shown, cell G6 contains this formula:

=SUMIF(C5:C11,"*"&F6&"*",D5:D11)

This formula sums amounts for items in column C that contain “hoodie”, anywhere in the cell.

Worked Example:   IF with boolean logic in Excel

How the formula works

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

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

So, for example, you can use “*hat*” to match the text “hat” anywhere in a cell, or “a*” to match values beginning with the letter “a”.

Worked Example:   9 Mathematical Computations Example using Excel Statistical Function

In this case, we want to match the text in F6. We can’t write the criteria like “*F6*” because that will match only the literal text “F6”.

Instead, we need to use the concatenation operator (&) to join a reference to F6 to asterisks (*):

"*"&F6&"*"

When Excel evaluates this argument inside the SUMIF function, it will “see” “*hoodie*” as the criteria:

=SUMIF(C5:C11,"*hoodie*",D5:D11)

SUMIF then returns the sum for items that contain “hoodie”, which is $27.00 in the example shown.

Worked Example:   Subtotal by invoice number in Excel

Note that SUMIF is not case-sensitive.

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,"*"&F6&"*")

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

Post navigation

Previous Post:

Sum if by year in Excel

Next Post:

Sum if cells contain specific text 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
  • How to split text string at specific character in Excel
  • How to extract domain from email address in Excel
  • How to extract last two words from text string in Excel
  • How to find and replace multiple values at same time in Excel
  • Split dimensions into three parts in Excel
  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • Get fiscal year from date in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date in Excel
  • Convert Excel time to Unix time in Excel
  • SYD function: Description, Usage, Syntax, Examples and Explanation
  • FVSCHEDULE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
  • DDB function: Description, Usage, Syntax, Examples and Explanation
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles