Excel SUMIF with Partial Match (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to sum up values based on specific criteria in another cell. In this tutorial, you will learn to use the SUMIF function based on a partial match in Excel.

Furthermore, we have included three different scenarios for a partial match in Excel. Basically, those scenarios are a partial match at the beginning, at the end, and at any position respectively. However, we will use only the SUMIF function throughout the whole article, to sum up, values based on a partial match in Excel.

Below, we have attached a screenshot as a summary of this article.

SUMIF with Partial Match in Excel


Download Practice Workbook

Here, you are recommended to download the Excel file and practice along with it.


The SUMIF Function: an Overview

Here, you can use the SUMIF function, to sum up, values that meet specific criteria. Now, for example, you want to add up all the values in a column that are greater than 20. In this case, you can easily sum up all the values by specifying the condition using the SUMIF function.

Syntax: 

SUMIF(range, criteria, [sum_range])

Arguments:

  • range: This field is mandatory. Here, you will input the range of the cells that you want to sum up altogether.
  • criteria: This is also a mandatory field. Here, you will specify the condition based on which you want to perform the sum operation within a cell range. In addition, you can specify the conditions as follows: 20, “>20”, F2, “15?”, “Car*”, “*~?”, or TODAY().
  • sum_range: This field is optional. Actually, in this field, you will input the cell range that you want to include in your sum formula by excluding the cell range specified by the range argument.

3 Ways to Use SUMIF Function Based on Partial Match in Excel

Here, we will be using a Product Price List data table to demonstrate all the methods to use the SUMIF function based on a partial match in Excel.

Dataset for Using SUMIF Function

In addition, we will use Microsoft 365 version for conducting the session. So, without having any further discussion let’s get into all the methods one by one.


1. Excel SUMIF: Partial Match at the Beginning

Here, in this section, you will learn to sum only if you find a match at the beginning of a cell value.  For example, we want to sum up the values of only those products from the Product Price List data table, whose Product ID possesses “MTT” in them. Now, let’s jump into the steps to see how it’s to be done.

🔗 Steps:

❶ First of all, select cell C16 ▶ to store the formula result.

❷ After that, type the formula within cell C16.

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

Use of SUMIF Function in Excel

❸ Now, hit the ENTER button.

That’s it.

Finding Partial Match at the Beginning with SUMIF Function

  Formula Breakdown: 

  • $B$5:$B$13 refers to the cell range of the Product ID column. Actually, within this range, we will look for the keyword “MTT”.
  • “MTT*” this is the keyword to search that must be included at the beginning of the product ids.
  • $D$5:$D$13 this is the sum range. Therefore, the summing operation is executed within this range.
  • =SUMIF($B$5:$B$13,"MTT*",$D$5:$D$13) returns the summation of the prices for only those products having the “MTT” keyword at the beginning of their Product IDs.
    • Output: $7,135.

Read More: How to Use Formula for Partial Number Match in Excel (5 Examples)


2. Excel SUMIF: Partial Match at the Ending

Now, we will calculate the summation of the prices for only the products, that have the keyword “NPP” at the end of their Product IDs. Here are the steps to follow:

🔗 Steps:

❶ First of all, select cell C16 ▶ to store the formula result.

❷ After that, type the formula.

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

Getting Total Price of Whose Matches Partially at the Ending of String

❸ Now, hit the ENTER button.

That’s it.

Employing Partial Match at the Ending within SUMIF Function

  Formula Breakdown: 

  • $B$5:$B$13 refers to the cell range of the Product ID column. Here, within this range, we will look for the keyword “NPP”.
  • “*NPP” this is the keyword to search that must be included at the end of the product ids.
  • $D$5:$D$13 this is the sum range. So, the summing operation is executed within this range.
  • =SUMIF($B$5:$B$13,"*NPP",$D$5:$D$13) returns the summation of the prices for only those products having the “NPP” keyword at the end of their Product IDs.
    • Output: $6,283.

Read More: Excel VLOOKUP to Find the Closest Match (with 5 Examples)


Similar Readings


3. Excel SUMIF: Partial Match at Any Position

Finally, we are going to discuss a universal formula that can perform the sum operation based on the partial match at any position. Now, for instance, you want to add the prices for only those products having the keyword “VX” in them at any position. Basically, all you can do is use the below formula specified in the second step.

🔗 Steps:

❶ First of all, select cell C16 ▶ to store the formula result.

❷ After that, type the formula within the cell.

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

Using SUMIF Function to Find Total Price of Some Particular Product

❸ Now hit the ENTER button.

That’s it.

Finding Total Price which Matches Partially at any Position.

  Formula Breakdown: 

  • Firstly, $B$5:$B$13 refers to the cell range of the Product ID column. Basically, within this range, we will look for the keyword “VX”.
  • Secondly, “*”&C15&”*” here the cell address C15 holds the keyword “VX”. Actually, you can use cell C15 as a search box, where you can input any keyword to search for and then sum up the corresponding prices of them.
  • Thirdly, $D$5:$D$13 this is the sum range. Therefore, the summing operation is executed within this range.
  • Lastly, =SUMIF($B$5:$B$13,"*"&C15&"*",$D$5:$D$13) returns the summation of the prices for only those products having the “VX” keyword at any position of their Product IDs.
    • Output: $5,876.

Read More: How to Find Partial Match in Two Columns in Excel (4 Methods)


Things to Remember

📌 First of all, beware of the position of the asterisk (*) in the criteria field.

📌 Additionally, make sure what range you are selecting for the range argument and what for the sum_range argument.


Practice Section

Now, you can practice by yourself.

Practice Section for Using SUMIF Function


Conclusion

To wrap up, we have discussed 3 different methods to use the SUMIF function based on a partial match in Excel. So, you are recommended to download the practice workbook attached along with this article and practice all the methods with that. However, don’t hesitate to ask any questions in the comment section below. In addition, we will try to respond to all the relevant queries asap. Also, you can visit our website Exceldemy to learn more Excel-related content.


Related Articles

Mrinmoy Roy

Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo