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 signs
Use 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 Oscar
You can contact me through this contact form