Excel Formulas - Count number of cells with only numbers

Count number of cells containing only numbers

Syntax of used function(s)

COUNT(value1, [value2], ...)

Count number of cells in a range that contain numbers.

Explanation

To count the number of cells containing numbers in a range of cells we can use the COUNT function.

Formula

=COUNT(TestData)

How the formula works

In the above formula COUNT function counts number of cells contain only number within the range B5:B11 named as TestData.

Count number of cells that does not contain numbers

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
ISNUMBER(value)

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.
The ISNUMBER funtion returns True if the value refers to a number.

Formula

=SUMPRODUCT(--NOT(ISNUMBER(TestData)))

How the formula works

In the above formula the ISNUMBER function search number in the range named as TestData and returns True or False.

The result like an array

{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;}

Therefore NOT convert it in reverse. The result like an array -

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;}

The -- convert the array into 1 and 0 like

{1;0;0;0;1;0;0;}

Therefore SUMPRODUCT add the array and returns result.

Previous: Excel Formulas - Count number of cells that contain errors
Next: Excel Formulas - Count number of cells ends with string