Here’s a dataset that contains the **Order No.**, **Item Names**, **Quantity** and **Sales** of some products. We’ll use it to create sums depending on certain values in the dataset. There’s a lookup cell and a result cell below the set (see image).

## Method 1 – Sum If Cell Contains Text in Another Cell Using the SUMIF Function

We will get the **Total Sales** of **Dark Chocolate.**

**Steps:**

- Go to cell
**C12**and use the following formula.

`=SUMIF(C5:C9,"*"&C11&"*",E5:E9)`

The ranges **C5:C9** and **E5:E9** indicate the **Item Names** and **Sales,** respectively. **C11** denotes the location of the cell containing the search keyword (**Dark Chocolate**).

- Press the
**Enter**key to get the**Total Sales**.

## Method 2 – Apply the SUMIFS Function to Add up If Cell Contains Text in Another Cell

We’ll get the **Total Sales** of **Dark Chocolate.**

**Steps:**

- Insert the following formula in
**C12**.

`=SUMIFS(E5:E9,C5:C9,"*"&C11&"*")`

- Hit
**Enter**.

## Method 3 – Sum If Cell Contains Specific Text in Excel Using Wildcards in the SUMIF Function

We’ll sum up the **Sales **of all items that contains the word **Dark** in the dataset.

**Steps:**

- Insert the formula below in cell
**C12**:

`=SUMIF(C5:C9,"*DARK*",E5:E9)`

Here, ‘***Dark***’ is the search keyword.

- Press
**Enter**to get the final output.

## Method 4 – Insert the SUMIFS Function with Wildcard to Add Cells Containing Specific Text

We’ll sum up the sales of items that contain the word **Dark** somewhere in the name.

**Steps:**

- Select cell
**C12**. - Insert the formula below in the cell:

`=SUMIFS(E5:E9,C5:C9,"*Dark*")`

- Press the
**Enter**key to get the result.

## Method 5 – Calculate the Total Price by Combining the SUM Function with SUMIF and SUMIFS Function

### Case 5.1 – Within One Column

Let’s calculate the **Total Sales** of **White Chocolate** and **Milk Chocolate.**

**Steps:**

- Go to cell
**C12**. - Insert the formula:

`=SUM(SUMIF(C5:C9,{"White Chocolate","Milk Chocolate"},E5:E9))`

- Press the
**Enter**key.

The **SUMIF** function will search for the keywords **White Chocolate** and **Milk Chocolate** within the **C5:C9** range. **E5:E9** is the sum range. If a cell in the C5:C9 range contains one of the keywords, its respective cell in the E5:E9 range will get added to the result.

**Read more: **How to Sum Names in Excel

### Case 5.2 – Among Several Columns

We’ll calculate the **Total Sales** of **Order No.** **4 **which is **Milk Chocolate**.

**Steps:**

- Click on cell
**C12**. - Use the formula below in the cell:

`=SUMIFS(E5:E9,B5:B9,"3",C5:C9,"Milk Chocolate")`

- Press the
**Enter**button to find the desired output.

Here, **E5:E9** is the sum range. **B5:B9** is the range where the **SUMIFS** function will find the number 3. Moreover, **C5:C9** refers to the range where the **SUMIFS** function will search for the words **Milk Chocolate**.

## Things to Keep in Mind

- There is no case sensitivity in the
**SUMIF**function.

**Download the Practice Workbook**

**Related Articles**

- How to Assign Value to Text and Sum in Excel
- How to Sum Text Values Like Numbers in Excel
- How to Sum Only Numbers and Ignore Text in Same Cell in Excel

**<< Go Back to Excel Sum If Cell Contains Text | Excel SUMIF Function | Excel Functions | Learn Excel**