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], ...)

## 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.

## 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.

## 4. Count non-empty values in an array 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. 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)

## 5. Count non-empty values based on a condition Formula in cell F3:

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

### Explaining formula

B3:B11=E3

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)

## 6. Count non-empty values 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)

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)

## 7. Count non-empty values in a 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)

## 8. Count non-empty cells in multiple cell 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)