Excel Formulas - Count number of cells which are not blank using COUNTA()

Count number of cells which are not blank using COUNTA()

Syntax of used function(s)

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

The COUNTA function is used to count the number of cells that are not empty in a range.

Explanation

To count the number of non-empty cells within a range or range name the function COUNTA can be used. In this example a list of department shown in column C and one or more of the listed cells are empty. The COUNTA function will count the number of non-blank cells within the range.

Formula

=COUNTA(C5:C11)

How the formula works

The COUNTA counts the number cells in the range that contains any value like text,number, date, error e.t.c. and return the result.

Count number of cells which are not blank using SUMPRODUCT() and LEN()

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
LEN(text)

The LEN funtion returns the number of characters from a text string.
The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.

Explanation

To count the number of non-empty cells within a range or range name the function SUMPRODUCT have used along with LEN function. In this example a list of departments shown in the range C5:C11.

Formula

=SUMPRODUCT(--(LEN(C5:C11)>0))

How the formula works

The LEN function find the number of characters for each cell in the range, therefore SUMPRODUCT counts the cells that contain atleast one character.
The SUMPRODUCT then returns an array with True and False. The '--' converts the True to 1 and False to 0. The SUMPRODUCT then sum the values and returns result.

Previous: Excel Formulas - Count cells for equality
Next: Excel Formulas - Count number of cells that contain errors