Summing up cell values based on partial text matches is crucial when generating total sales or profits from a humongous dataset.
In Microsoft Excel, users can utilize several functions to perform comparatively complex calculations. For adding values based on criteria, we can use the SUMIF function.
In this article, I will discuss all the ins and outs of the Excel SUMIF formula and demonstrate how to use the formula with partial text match.
Without further ado, let’s begin!
A Brief Introduction to SUMIF Function of Excel
The SUMIF or SUMIFS functions of Microsoft Excel are math and trig functions used to calculate the sum of multiple cells based on conditions. For instance, using the SUMIF functions, you can calculate companies’ total sales with ‘XX’ characters in their ID.
Both the functions are almost identical. The only difference is that the SUMIF can check only one criterion, and the SUMIFS can check multiple criteria at once.
In this article, we will perform calculations based on one criterion. Now, let’s have a look at the following formula.
=SUMIF(B5:B9,”XX”,C5:C9)
If we break down the formula, we will get four different parts. Here, =SUMIF is the main function, B5:B9 is the value range, “XX” is the criteria, and C5:C9 is the sum range.
Consider the general syntax of the function:
=SUMIF(range, criteria, [sum_range])
Here, the value range B5:B9 is the cells where your data are stored, and the sum range C5:C9 is the cells that contain the value data. Most importantly, “XX” is the criteria (value to check) that defines which cell values to sum if the cells contain text values (XX).
Now that you have a basic understanding of the function, move down to the next section to sum with a specific or partial text match.
How to Use SUMIF with Partial Text Match in Excel
To add cells containing partial text matches, select the cell where you want the result, choose the cell range, type the criteria, and select the sum cell range. Once you’ve completed the formula, press the Enter button to sum up the cells containing partial text matches.
The function is used to find the SUM of a particular cell range depending on a specified condition.
Alternatively, you can use the SUMIFS function if you need to sum up based on multiple criteria.
Here are the methods to SUM based on Partial Text Match:
Sum If Cell Contains Partial Text
When you need to sum based on a partial text match, you can use the SUMIF function. For instance, we will sum up the sale of companies with the character ‘Y’ in the Company ID.
To do so, select the cell where you want to show the sum result and enter the following formula in the formula box.
=SUMIF(B5:B9,”*Y*”,C5:C9)
This formula will show the sum of C6:C10 cells based on B6:B10 cells. Remember, it will only add the cells with the letter ‘Y’ in the Company ID. No matter where the character is in the company ID cells.
Quick Note: The asterisk (*) in the formula denotes that the formula will ignore any characters before and after “9Z” and sum up the cells if the “9Z” appears anywhere within the text in the cells range.
Sum Values If Cell Contains Specific Text
If you need to sum up the cells containing a specific text, the SUMIF function is the best shot. For example, we will sum up all the calls that have the text ‘XX’ as the company ID.
To achieve this, choose the cell where you want to get the result and type the following formula in the Excel formula box.
=SUMIF(B5:B9,”XX”,C5:C9)
This formula will display the sales summation for the companies with the ‘XX’ as the Company ID. The above formula will only work with text values.
However, if the criteria don’t match with any cells, this formula won’t work and will show you the value 0. So, you better know how to use SUMIF to calculate a value greater than zero and ensure that the Company ID has the cells with the exact same text as the criteria you’re using in the formula.
Add Values If Cell Contains Number and Text
Sometimes, you may need to sum up cell values containing both numbers and text. Fortunately, using the SUMIF function, you can effortlessly accomplish that.
Suppose you want to add the sales for companies with the letter and number combo “9Z” in the Company ID. In that case, select the desired cell and type the following formula in the Excel formula bar.
=SUMIF(B5:B9,”*”&”9Z”&”*”,C5:C9)
This formula will look for the ‘9Z’ value in the B7:B10 cells, and if it finds any match, it will count those cells and show the total sales of those companies.
In summary, this formula sums up the values of C7:C10 for the cells B7:B10 that contain ‘9Z’ anywhere in their text.
Wrap Up
The SUMIF function of MS Excel is the ultimate tool for adding values based on a specific condition. Throughout this article, I have demonstrated how to sum values with specific text, partial text match, and combined data (number and text).
If you need further assistance regarding this topic, don’t hesitate to comment below.
