This guide will explain how to count cells that contain numbers in a range using the Excel COUNT function.
In the example, we want to count all cells in a range where the cells are numbers. Two common methods are using the COUNT function or applying the SUMPRODUCT function. If you want to learn all about Excel formulas, check out our tutorial.
How to count cells that contain numbers
If you want to count numbers in a given range, use the COUNT function.
First, let us see the generic formula:
=COUNT(range)In the example shown, cell D3 contains this formula. The range is B3:B9, and we find the cells that have numbers.
Formula:
=COUNT(B3:B9)After evaluating the formula, the expression returns with 5 since we have three text and five numeric values. The COUNT function uses a single argument, ‘range’. The function skips the empty cells and only counts cells that contain numeric values. The function will exclude cells B6 and B7 because they contain text values. Use the COUNTA function to count all non-empty cells.
Using SUMPRODUCT
Alternatively, you can count cells containing numbers using the SUMPRODUCT function and ISNUMBER function.
Formula:
=SUMPRODUCT(--ISNUMBER(B3:B9))Evaluate the formula from the inside out. ISNUMBER function will check all cells in the selected range, B3:B9.
=ISNUMBER(B3:B9)The B3:B9 range contains 7 values; the ISNUMBER function returns with an array that contains TRUE or FALSE values.
={TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE}Based on the result, TRUE values show the cells that contain numbers. In the case of FALSE results, they do not contain numeric values. The problem is that the SUMPRODUCT function works with numbers only, so we have to convert the TRUE and FALSE values to 1s and 0s.
Use the double negative method to convert the boolean values to numbers.
=--{TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE}The SUMPRODUCT function will use the converted array as an argument:
=SUMPRODUCT(--{1, 1, 1, 0, 0, 1, 1})SUMPRODUCT returns 5 as a result.
How to count cells that do not contain numbers
Append the current formula with the NOT function to count cells that do not contain numeric values.
=SUMPRODUCT(--NOT(ISNUMBER(B3:B9)))Using the NOT function, you will get an inverse output from the ISNUMBER section, like in the picture below:
=SUMPRODUCT(0,0,0,1,1,0,0)
In the given range, we have two cells that do not contain numbers; the result is 2.
