Excel Sum If Cell Contains Text (Easiest Way in 2024)
In this article, we will show you how to use the Excel SUMIF for a cell that contains text. Simply follow the steps below.
Excel SUMIF Contains Text
The SUMIF function in Excel can be used to add up values based on a condition, such as when a cell contains specific text.
Syntax
The general syntax for the SUMIF function in Excel is as follows:
=SUMIF(range, criteria, [sum_range])
where:
range: This is the range of cells that you want to evaluate based on the criteria. Excel will check each cell in this range against the criteria.
criteria: This is the condition that must be met for Excel to include a cell in the calculation. It can be a number, expression, or text enclosed in double quotes.
[sum_range]: This argument specifies the actual cells that Excel will sum if the corresponding cells in the range meet the criteria.
Using Excel SUMIF when Cell Contains Specific Text
In order to sum cells that contain specific text, simply follow the steps below.
1. Choose the Clothing Item for Analysis
For our example, decide which clothing item you want to analyze. Let’s choose “T-shirts.” This will be the text criteria we use to find the total quantity of T-shirts in the data set.
2. Use SUMIF to Calculate Total Quantity
In a new cell (let’s say D2), use the SUMIF formula to calculate the total quantity of the chosen clothing item. The formula looks like this:
=SUMIF(A2:A11, “T-shirts”, B2:B11)
This formula tells Excel to sum the quantities from the “Quantity” column where the corresponding cells in the “Item” column contain “T-shirts.”
3. Press Enter to See the Result
After entering the formula, press Enter. Excel will display the total quantity of “T-shirts” in your data set. This is the number of T-shirts present based on the data you provided.
How to Sum if a Cell Contains Partial Text
To sum a cell that contains partial text, use the SUMIF function. Here’s how to do it:
1. Choose the Partial Text Criteria
Decide on the specific part of the text that you want to use for your analysis. For example, if you’re interested in all items that have “T-shirt” in their description (like “Red T-shirt” or “White T-shirt”), then “T-shirt” is your partial text criteria. This criteria helps Excel know what to look for when summing up quantities.
2. Use the SUMIF Function for Partial Text
Use Excel’s SUMIF function to add up the quantities based on your partial text criteria. In a new cell (let’s say D2), enter the following formula:
=SUMIF(A2:A11, “*T-shirt*”, B2:B11)
This formula tells Excel to look in the “Item” column (A2:A11) for any text that contains “T-shirt” and then sum up the corresponding quantities from the “Quantity” column (B2:B11). The asterisk (*) acts as a wildcard character, which means Excel will match any text that includes “T-shirt” anywhere within the cell’s content.
3. Press Enter to Calculate
After typing the formula, press the Enter key. Excel will calculate the sum based on your partial text criteria. You’ll see the total quantity of items that match your criteria (in this case, items with “T-shirt” in their description).
Using Excel Sum if Cell Contains Text and Numbers
Follow the process below to easily use the SUMIF function to sum cells that contain text and numbers.
1. Choose the Criteria for Summing
Decide on the specific criteria you want to use for the SUMIF function. For this tutorial, we’ll use “123” as the criteria. This means we’ll sum the quantities for items that contain this number in their description.
2. Use the SUMIF Function for Summing
In a new cell (D2), enter the following SUMIF formula:
=SUMIF(A2:A11, “*123*”, B2:B11)
This formula instructs Excel to sum the quantities from Column B, where the corresponding cells in Column A contain the number “123” anywhere within the text. The asterisk (*) acts as a wildcard, allowing flexibility in matching text.
3. Press Enter to Calculate the Sum
After entering the formula, press Enter. Excel will calculate and display the sum of quantities for items that contain “123” in their description. This step finalizes the calculation based on your criteria.
We hope that you now have a better understanding of how to use Excel Sum if a cell contains text. If you enjoyed this article, you might also like our article on how to use Excel SUMIF with wildcard characters or our article on how to import data from picture to Excel.
