This article covers how to use **the SUMIF function** with wildcard to sum cells based on criteria in **Google Sheets**. You may sum cells based on a variety of criteria with text, numbers, and dates. Using wildcards makes the criteria more dynamic. This also helps to keep the formulas neat and concise. Today, we’ll take a closer look at using wildcards with the **SUMIF function**.

**Table of Contents**hide

## A Sample of Practice Spreadsheet

You can make a copy of our dataset from the download button below to practice yourself.

## What Is Wildcard in Google Sheets?

Wildcards are special characters in Google Sheets that are used to represent other characters. They are very useful while entering criteria for conditional functions (**SUMIF, COUNTIF,** etc) or searching for approximately matching strings in Google Sheets.

Usually while using Google Sheets to make calculations, we need to determine the exact string that should go into the formula. Moreover, we may need to search for values that may include any particular string. Fortunately, in both cases, Google Sheets wildcards can be of great benefit. To assist you to find the most relevant search results possible, wildcards are special symbols that can represent one or more string characters. You can use the following 3 wildcards in Google Sheets.

**The Asterisks(*):**This character can take on any number of forms.**Question Mark(?):**A single character is symbolized by this wildcard.**Tilde(~):**This wildcard is used to instruct Google Sheets that the following character should not be treated as a wildcard but rather as a regular character symbol. It is typically used before one of the above wildcard characters. (*****or**?**).

## 6 Examples of Using SUMIF Function with Wildcard in Google Sheets

Now, you can have a look at our dataset which contains three columns including **Sales Person**, **Product Code,** and **Sales**. In this article, we will show you how to use the **SUMIF** **function** with wildcard to get the sum of sales based on different criteria.

Follow the examples below to learn how to do that.

### 1. For Partial Match

#### 1.1 Beginning of Name

Suppose you need to find the total sales made by the salespersons whose names start with certain characters. You can use the **Asterisks(*) **wildcard after the characters in the criteria of the **SUMIF function** to do that. Here is the example below:

- Consider you need to get the sum of
**sales**made by**Sales Persons**whose names start with**Steve**. Here, two of the salesperson’s names start with**“Steve”**.

- Then you can just apply the following formula in cell
**G5**to get the desired output as shown below.

`=SUMIF(B5:B10,"Steve*",D5:D10)`

- Here we use the
**asterisks(*)**wildcard after**Steve**as**Steve***to suggest that the name must start with**Steve**and the rest of the characters can be anything. Also, notice that the criterion is enclosed with double quotes as**“Steve*”.**

#### 1.2 End of Name

Now assume you need to find the total **Sales** made by the **Sales Persons** whose names end with certain characters. You can use the **Asterisks(*) **wildcard before those characters in the criteria of the **SUMIF function** to do that. Here is the example below:

- Consider you need to get the sum of sales made by salespersons whose names end with
**Gill**. Here, two of the salesperson’s names end with**“Gill”**.

- Then apply the following formula in cell
**G5**to get the following result.

`=SUMIF(B5:B10,"*Gill",D5:D10)`

- Here we use the
**Asterisks(*)**wildcard before**Gill**as***Gill**to suggest that the names may begin with any number of characters. But it must end with**Gill**.

#### 1.3 Middle of Name

You can also use the **asterisks(*)** wildcard to sum **Sales** made by salespersons with a particular middle name. You need to use the ***** wildcard before and after the middle name in the criteria for that.

- Suppose you want to get the total sales by a salesperson whose middle name is
**Mason**. The dataset contains two such cells as highlighted below.

- Then enter the following formula in cell
**G5**to get the result below.

`=SUMIF(B5:B10,"*Mason*",D5:D10)`

### 2. Criteria Contain Wildcard Characters

Now suppose you need to find the total sales of particular products whose **Product Codes** contain certain wildcard characters. Then you need to use the **Tilde(~)** wildcard before that wildcard character in the criteria of the formula. Here is the example below:

- First, have a look at our
**dataset**. Assume you want to sum the sales for the product code**PC 32***. If you directly use this as the criteria in the formula, it will also sum the sales for**PC 3249**.

- You need to use the
**Tilde (~)**wildcard before the*****in the criteria. This is to verify that the*****itself is a normal character rather than a wildcard. Apply the formula in cell**G5**to see the desired result.

`=SUMIF(C5:C10,"PC 32~*",D5:D10)`

### 3. Have Specific Number of Characters

In this example, we will highlight how to sum the **Sales** if the **Product Codes** contains a specific number of character in specific positions. You need to use the **Question Mark(?)** wildcard in the criteria to do that.

- Consider you need to get the total
**Sales**for**Product codes**starting and ending with**M**and**B**, and containing any three-digit numbers in between them. Here,**M450B**and**M472B**contain match those criteria.

- You need to use
**3**question marks (**?**) as the wildcard in the criteria of the formula. Apply the following formula in cell**G5**to get the following output.

`=SUMIF(C5:C10,"M???B",D5:D10)`

### 4. Criteria with Wildcard and Cell Reference

Now, we will highlight using the **SUMIF function** with wildcard and **cell reference** in Google Sheets. Suppose, you want to know the sum of the sales of a salesperson with similar middle names. Have a look at our step-by-step procedure below.

- Consider you need to get the total
**Sales**for**Sales_Person**having a similar middle name**“Mason”**, so we highlighted the cells containing this name.

- Then, we use
**Mason**as criteria and get the formula using a wildcard with cell reference in the**SUMIF**formula. Here, we use cell**F5**as a reference.

`=SUMIF(B5:B10,"*"&F5&"*",D5:D10)`

- As we use cell reference, after that, if we want to get other values, we can just change the criteria in cell
**F5**and the sum of the values will update automatically. Here, the formula is slightly changed as these names contain the specific character at the end, so we will refer first part using**asterisks(*)**and for the remaining part we will use a cell reference.

`=SUMIF(B5:B10,"*"&F5,D5:D10)`

## Things to Remember

- Put the criterion in quotation marks if it
**contains text**, a wildcard character, or a logical operator that is then followed by a number, text, or date. - Use quotation marks to introduce a text string and an ampersand (&) to concatenate and end it if the criterion calls for both a logical operator and a cell reference or another function.

## Conclusion

In this article, we have discussed how to use the **SUMIF function** with wildcard in Google Sheets. Here, we have mentioned 6 examples of how you can use wildcards along with SUMIF to get the sum result of a specific range. We hope, it was helpful to you. Please, don’t forget to visit our **OfficeWheel** website to know more details about Google Sheets to make your work easier. Leave a comment if you have more examples to do this and if you faced any problems understanding any of the examples we mentioned.

**Related Articles**

**How to Use Wildcard in Google Sheets (3 Practical Examples)****[Fixed!] Wildcard Is Not Working in Google Sheets (5 Solutions)****How to Use Filter with Wildcard in Google Sheets (3 Examples)****Use Wildcard with IF Condition in Google Sheets (3 Ways)****How to Use QUERY Function with Wildcard in Google Sheets****Find and Replace with Wildcard in Google Sheets**