Sum Only If Value in Excel Cell Is Number and Not Text

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.


Description automatically generated

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:

  1. Select cell B6 and type in the following formula:
Graphical user interface, application, table, Excel

Description automatically generated
  1. Press the Enter key on the keyboard or click the Enter button on the formula bar.
Graphical user interface, application

Description automatically generated

The SUM function returns the sum of the two numeric values in the range B2:B2 and ignores the text values.


Description automatically generated

Note: The SUM function by default ignores text values in the sum range.


This tutorial showed how to sum values in Excel cells only if they are numbers and not text using the SUM function.