Table of Contents
In Excel, the SUM function allows users to add up numbers in a given range. However, if the cells in that range contain both text and numbers, the SUM function will return an error. To sum cells in Excel that contain both text and numbers, the CONCATENATE and VALUE functions can be used in conjunction with the SUM function. The CONCATENATE function combines the text and numbers into a single string, which can then be converted into a number using the VALUE function. This number can then be summed using the SUM function, allowing for the addition of cells containing both text and numbers.
Excel: Sum Cells with Text and Numbers
You can use the following basic formula to sum cells in Excel that contain both numbers and text:
=SUM(SUBSTITUTE(B2:B8, "some_text", "")+0)
This particular formula removes the text string “some_text” from each cell in the range B2:B8 and then calculates the sum of the values in the range B2:B8.
The following examples show how to use this formula in practice.
Example 1: Calculate Sum of Cells with Text and Numbers
Suppose we have the following dataset that shows the total number of sales at seven different stores:
To calculate the sum of sales, we can type the following formula into cell B10:
=SUM(SUBSTITUTE(B2:B8, " items", "")+0)Once we press Enter, the sum of the items will be shown:
The sum of the items sold is 97.
This formula simply substituted a blank where “items” used to be in each cell and then calculated the sum of the values remaining in the cells.
Example 2: Calculate Sum of Cells with Different Text and Numbers
Suppose we have the following dataset that shows the total number of sales at seven different stores:
To calculate the sum of sales, we can type the following formula into cell B10:
=SUM(SUBSTITUTE(SUBSTITUTE(B2:B8, " items", ""), "things", "")+0)Once we press Enter, the sum of the values in column B will be shown:
The sum of the items sold is 97.
This formula simply substituted a blank where “items” and “things” used to be in each cell and then calculated the sum of the values remaining in the cells.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I sum cells in Excel that contain both text and numbers?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-sum-cells-in-excel-that-contain-both-text-and-numbers/
stats writer. "How can I sum cells in Excel that contain both text and numbers?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-sum-cells-in-excel-that-contain-both-text-and-numbers/.
stats writer. "How can I sum cells in Excel that contain both text and numbers?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-sum-cells-in-excel-that-contain-both-text-and-numbers/.
stats writer (2024) 'How can I sum cells in Excel that contain both text and numbers?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-sum-cells-in-excel-that-contain-both-text-and-numbers/.
[1] stats writer, "How can I sum cells in Excel that contain both text and numbers?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I sum cells in Excel that contain both text and numbers?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.