## How to use the COUNTA function

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

#### Table of Contents

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

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

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

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

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

### 'COUNTA' Function examples

The following 17 articles have formulas containing the COUNTA function.

This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

This article describes how to create a random playlist based on a given number of teams using an array formula. […]

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]

The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]

This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]

This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

### Functions in 'Statistical' category

The COUNTA function function is one of many functions in the 'Statistical' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form