In this tutorial, we will describe how to use** the SUMIF function** based on a partial match in three different scenarios: at the beginning of a value, at the end, and at any position within the value respectively.

Here is a quick summary of what we’ll cover:

## Overview of the SUMIF Function

The** SUMIF **function is used to sum values that meet a specific criterion. For example, if we want to add up all the values in a column that are greater than 20, we would specify that as the condition in the **SUMIF** function.

**Syntax: **

**SUMIF(range, criteria, [sum_range])**

**Arguments:**

**range:**The range of cells to be added together (required).**criteria:**The condition based on which to perform the sum operation within the**range**(required). The conditions can be specified as follows:**20**,**“>20”**,**F2**,**“15?”**,**“Car*”**,**“*~?”**, or**TODAY()**.**sum_range:**The cell range to include in the sum formula, excluding the**range**argument (optional).

## How to Use the SUMIF Function for a Partial Match in Excel: 3 Ways

We’ll use the following **Product Price List** data table to demonstrate our methods.

### Method 1 – Using SUMIF for a Partial Match at the Beginning

In our first example, we’ll sum only if we find a match at the beginning of a cell value. For example, let’s add up the values of only those products from the **Product Price List** table whose **Product ID** starts with “MTT”.

**Steps:**

- Select cell
**C16**to store the formula result. - Enter the following formula in cell
**C16**:

`=SUMIF($B$5:$B$13,"MTT*",$D$5:$D$13)`

- Press
**ENTER**.

**Formula Breakdown: **

**$B$5:$B$13****:**refers to the cell range of the**Product ID**column. Within this range, we will look for the keyword “MTT”.**“MTT*”****:**the keyword to search that must appear at the beginning of the product IDs.**$D$5:$D$13****:**the sum range where the summing operation will be executed.`=SUMIF($B$5:$B$13,"MTT*",$D$5:$D$13`

)**:**returns the sum of the prices for only those products having the “MTT” keyword at the beginning of their Product IDs.**Output: $7,135.**

### Method 2 – Using SUMIF for a Partial Match at the End

Now, we will calculate the sum of the prices of only the products that have the keyword “NPP” at the end of their** Product IDs**.

**Steps:**

- Select cell
**C16**to store the formula result. - Enter the following formula:

`=SUMIF($B$5:$B$13,"*NPP",$D$5:$D$13)`

- Press
**ENTER**.

**Formula Breakdown: **

**$B$5:$B$13****:**the cell range of the**Product ID**column, where we will look for the keyword “NPP”.**“*NPP”****:**the keyword to search that must appear at the end of the product IDs.**$D$5:$D$13****:**the sum range where the summing operation will be executed.`=SUMIF($B$5:$B$13,"*NPP",$D$5:$D$13)`

**:**returns the sum of the prices of only those products having the “NPP” keyword at the end of their Product IDs.**Output: $6,283**.

### Method 3 – Using SUMIF for a Partial Match at Any Position

In this last example, we’ll present a universal formula that can perform the sum operation based on a partial match at any position in a cell value. For example, let’s add the prices of only those products containing the keyword “VX” in any position in their Product ID.

**Steps:**

Select cell **C16 **to store the formula result.

Enter the following formula in the cell:

`=SUMIF($B$5:$B$13,"*"&C15&"*",$D$5:$D$13)`

Press **ENTER**.

**Formula Breakdown: **

**$B$5:$B$13****:**the cell range of the**Product ID**column, where we will look for the keyword “VX”.**“*”&C15&”*”****:**the cell address**C15**holds the search keyword “VX”. You can use cell**C15**as a search box, where you can input any keyword to search for and then sum the corresponding prices of matched values in the**range**.**$D$5:$D$13****:**the sum range where the summing operation will be executed.`=SUMIF($B$5:$B$13,"*"&C15&"*",$D$5:$D$13)`

**:**returns the sum of the prices of only those products having the “VX” keyword in any position within their Product IDs.**Output: $5,876**.

## Things to Remember

- Be careful with the positioning of the
**asterisk (*)**in the criteria field. - Make sure the range you are selecting for the
**range**and**sum_range**arguments are correct.

**Download Practice Workbook**

**<< Go Back to Partial Match Excel | Formula List | Learn Excel**