Table of Contents
The process of calculating the sum of cells that contain partial text in Excel involves using the SUMIF function. This function allows you to specify a range of cells and a criteria, and it will add up only the cells that meet that criteria. In this case, the criteria will be the partial text that you want to search for within the cells. By using this function, you can easily find the total sum of all cells that contain the specified partial text, providing a quick and efficient way to analyze data in Excel.
Excel: Calculate Sum If Cell Contains Partial Text
You can use the following formula to calculate the sum in Excel only for cells that contain a specific partial text:
=SUMIF(A1:A13,"*text*",B1:B13)
This particular formula will calculate the sum of the values in the range B1:B13 only for the cells that contain “text” in the range A1:A13.
Note: The asterisks are wildcard characters that tell Excel to ignore any text before or after a specific string.
The following example shows how to use this formula in practice.
Example: Calculate Sum If Cell Contains Partial Text
Suppose we have the following dataset that contains information about the sales made at various retail stores:
We can use the following formula to calculate the sum of sales only for the stores that contain the partial text “Tier 1” somewhere in the store name:
=SUMIF(A2:A11,"*Tier 1*",B2:B11)
The following screenshot shows how to use this formula in practice:
The sum of sales made by stores that contain “Tier 1” in the name is 109.
We can manually verify this is correct by identifying each of the stores that have “Tier 1” in the name:
We can then calculate the sum of sales for these stores only:
Sum of Tier 1 Store Sales: 24 + 22 + 37 + 26 = 109.
Also note that this formula is case-insensitive.
For example, we could use “*TIER 1*” or “*tier 1*” or “*Tier 1*” in the formula and Excel will return the same result.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I calculate the sum of cells that contain partial text in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-that-contain-partial-text-in-excel/
stats writer. "How do I calculate the sum of cells that contain partial text in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-that-contain-partial-text-in-excel/.
stats writer. "How do I calculate the sum of cells that contain partial text in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-that-contain-partial-text-in-excel/.
stats writer (2024) 'How do I calculate the sum of cells that contain partial text in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-the-sum-of-cells-that-contain-partial-text-in-excel/.
[1] stats writer, "How do I calculate the sum of cells that contain partial text in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate the sum of cells that contain partial text in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.