Author: Oscar Cronquist Article last updated on April 19, 2022

The COUNTA function counts the non-empty or blank cells in a cell reference.

1. COUNTA Function Syntax

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

Back to top

2. COUNTA Function Arguments

value1 Required. A cell reference to a range for which you want to count not empty values.
[value2] Optional. Up to 254 additional arguments like the one above.

The COUNTA function counts errors as not empty.

Back to top

3. COUNTA Function example

Formula in cell F3:

=COUNTA(C3:C10)

The picture above demonstrates the COUNTA function entered in cell F3. The evaluated range is C3:C10, two of the cells contain formulas, three cells seem to be empty but only one is in fact empty.

Cell C4 has a formula that returns a blank, note that the COUNTA function considers this cell not empty. Cell C7 has a space character and is therefore counted. Only cell C6 is empty.

Back to top

4. Count non-empty values in an array

COUNTA function arrays

The COUNTA function returns an error dialog box if at least one of the containers is empty.

Formula in cell B3:

=COUNTA({"A";;"";44;0;" ";TRUE;#DIV/0!;"Text"})

The formula above does not work, however, the formula below works.

COUNTA function arrays1

The COUNTA function works if all containers in the array are non-empty.

Formula in cell B3:

=COUNTA({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"})

An empty container like this "" is not considered empty which is surprising.

The following formula counts non empty values in a hardcoded array.

=SUMPRODUCT((IFERROR({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"},"A")<>"")*1)

Explaining formula

Step 1 - Replace errors with a text value

IFERROR({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"},"A")

Step 2 - Check if not empty

(IFERROR({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"},"A")<>""

Step 3 - Convert boolean values to the numerical equivalents

(IFERROR({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"},"A")<>""

Step 4 - Convert boolean values to the numerical equivalents

(IFERROR({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"},"A")<>"")*1

Step 5 - Calculate a total

SUMPRODUCT((IFERROR({"A";"";44;0;" ";TRUE;#DIV/0!;"Text"},"A")<>"")*1)

Back to top

5. Count non-empty values based on a condition

COUNTA function based on a condition

Formula in cell F3:

=SUM(IF(B3:B11=E3,C3:C11<>"",0)*1)

Explaining formula

Step 1 - Logical expression

B3:B11=E3

Step 2 - Check if not empty (non-empty)

C3:C11<>""

Step 3 - Evaluate IF function

IF(B3:B11=E3,C3:C11<>"",0)

Step 4 - Convert boolean values

IF(B3:B11=E3,C3:C11<>"",0)*1

Step 5 - Calculate a total

SUM(IF(B3:B11=E3,C3:C11<>"",0)*1)

Back to top

6. Count non-empty values based on a list

COUNTA function based on a list

Formula in cell F3:

=SUM(IF(COUNTIF(E3:E4, B3:B11), C3:C11<>"", 0)*1)

Explaining formula

Step 1 - Which values equals any item in the list

COUNTIF(E3:E4,B3:B11)

Step 2 - Check if not empty (non-empty)

C3:C11<>""

Step 3 - Evaluate IF function

IF(B3:B11=E3,C3:C11<>"",0)

Step 4 - Convert boolean values

IF(B3:B11=E3,C3:C11<>"",0)*1

Step 5 - Calculate a total

SUM(IF(B3:B11=E3,C3:C11<>"",0)*1)

Back to top

7. Count non-empty values in a string

COUNTA function string

Excel 365 dynamic array formula in cell C3:

=SUM((TEXTSPLIT(C3,";")<>"")*1)

Explaining formula

Step 1 -

TEXTSPLIT(C3,";")

Step 2 -

TEXTSPLIT(C3,";")<>""

Step 3 -

(TEXTSPLIT(C3,";")<>"")*1

Step 4 -

SUM((TEXTSPLIT(C3,";")<>"")*1)

Back to top

8. Count non-empty cells in multiple cell ranges

COUNTA function multiple source ranges

Formula in cell B12:

=SUM((VSTACK(B3:B9,D3:D9,F3:F9)<>"")*1)

Explaining formula

Step 1 - Join arrays

VSTACK(B3:B9,D3:D9,F3:F9)

Step 2 - Check if not empty

VSTACK(B3:B9,D3:D9,F3:F9)<>""

Step 3 - Convert boolean values to numbers

(VSTACK(B3:B9,D3:D9,F3:F9)<>"")*1

Step 4 - Calculate a total

SUM((VSTACK(B3:B9,D3:D9,F3:F9)<>"")*1)

Back to top