How to use the COUNTA function
Author: Oscar Cronquist Article last updated on April 19, 2022
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.
Apply drop-down lists dynamically
This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]
This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The […]
Automate net asset value (NAV) calculation on your stock portfolio
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
Create a dynamic named range
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
Create a random playlist
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Create number series
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
Excel has a great built-in tool for creating number series named Autofill. The tool is great, however, in some situations, […]
Extract records where all criteria match if not empty
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
This article explains how to extract values that exist in three different columns, they must occur in each of the […]
How to build a Team Generator – different number of people per team
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]
JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]
How to create a dynamic chart
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
How to use the COUNTIF function to count not blank cells
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
List permutations with repetition and how many to choose from
This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]
This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]
Lookup with any number of criteria
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
Use drop down lists and named ranges to filter chart values
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
Wildcard lookups and include or exclude criteria
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
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.
Calculates the average of the absolute deviations of data points from their mean.
Calculates the average of numbers in a cell range.
Returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.
Returns the average of cell values that are valid for a given condition.
Returns the average of cell values that evaluates to TRUE for multiple criteria.
Calculates the beta distribution.
Calculates the inverse of the cumulative beta distribution.
Calculates the individual term binomial distribution probability.
Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.
Calculates the probability of the chi-squared distribution, cumulative distribution or probability density.
Calculates the right-tailed probability of the chi-squared distribution.
Calculates the inverse of the left-tailed probability of the chi-squared distribution.
Calculates the inverse of the right-tailed probability of the chi-squared distribution.
Calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees of freedom. Use this function to check if hypothesized results are valid.
Calculates the confidence interval for a population mean.
Calculates the confidence range for a population mean using a Student's t distribution.
Calculates the correlation between two groups of numbers.
Counts all numerical values in an argument.
Counts the non-empty or blank cells in a cell range.
Counts empty or blank cells in a range.
Calculates the number of cells that is equal to a condition.
Calculates the number of cells across multiple ranges that equals all given conditions.
Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the sample covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the exponential distribution representing an outcome in the form of probability.
Calculates the F probability for two tests.
Calculates the right-tailed F probability for two tests.
Calculates the two-tailed probability from an F-test
Calculates a value based on existing x and y values using linear regression.
Calculates how often values occur within a range of values and then returns a vertical array of numbers.
Calculates the GAMMA value.
Calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution.
Calculates the geometric mean.
Returns estimated exponential growth based on given data.
Returns a value representing the y-value where a line intersects the y-axis.
Calculates the k-th largest value from an array of numbers.
Returns an array of values representing the parameters of a straight line based on the "least squares" method.
Returns an array of values representing the parameters of an exponential curve that fits your data, based on the "least squares" method.
Calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.
Calculate the largest number in a cell range.
Calculates the highest value based on a condition or criteria.
Calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
Returns the smallest number in a cell range.
Returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).
Calculates the smallest value based on a given set of criteria.
Returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.
Calculates the most frequent value in an array or range of data.
Calculates the normal distribution for a given mean and standard deviation.
Calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.
Calculates the percent rank of a given number in a data set.
Calculates the percent rank of a given number compared to the whole data set.
Returns the number of permutations for a set of elements that can be selected from a larger number of elements.
Returns the number of permutations for a specific number of elements that can be selected from a larger group of elements.
Calculates a number of the density function for a standard normal distribution.
Returns the quartile of a data set.
Returns the quartile of a data set, based on percentile values from 0..1, inclusive.
Returns the rank of a number out of a list of numbers.
Calculates the rank of a number in a list of numbers, based on its position if the list were sorted.
Calculates the skewness of a group of values.
Calculates the slope of the linear regression line through coordinates.
Returns the k-th smallest value from a group of numbers.
Calculates a normalized value from a distribution characterized by mean and standard_dev.
Returns standard deviation based on the entire population.
Returns standard deviation based on a sample of the entire population.
Estimates the standard deviation from a sample of values.
Returns the standard deviation based on the entire population, including text and logical values.
Calculates values along a linear trend.
Calculates the mean of the interior of a data set.
Returns the variance based on the entire population. The function ignores logical and text values.
The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.
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