Sometimes we have a dataset that contains both numeric and text values in a column. We may want to sum only the numeric values and ignore the text values. This tutorial shows how to sum values in Excel cells only if they are numbers and not text.
How to Sum Numbers and Ignore Text in Excel
We use the SUM function to sum only numeric values in Excel cells.
Suppose we have the following dataset that shows the amount of annual rainfall in different regions. Text values were erroneously entered in some cells.
We want to sum the numbers in the range B2:B5 and ignore the text values that were entered erroneously.
We follow the steps below:
- Select cell B6 and type in the following formula:
|
1 |
=SUM(B2:B5) |
- Press the Enter key on the keyboard or click the Enter button on the formula bar.
The SUM function returns the sum of the two numeric values in the range B2:B2 and ignores the text values.
Note: The SUM function by default ignores text values in the sum range.
Conclusion
This tutorial showed how to sum values in Excel cells only if they are numbers and not text using the SUM function.