How to use the COUNT function

Author: Oscar Cronquist Article last updated on March 23, 2023

The COUNT function counts all numerical values in an argument, it allows you to have up to 255 arguments.

Formula in cell D3:

=COUNT(B3:B8)

Blank cells, boolean values and text values are not counted. It differs from most other functions, ignoring error values.

COUNT function Syntax
COUNT function Arguments
COUNT function - example
COUNT function - count numbers in an array
COUNT function - count digits
COUNT function - count digits in cell range
COUNT function - count negative numbers
COUNT function - count positive numbers
COUNT function - count numbers in a given range
Count numbers per row
Sort rows based on numbers
Count numbers per column
Sort columns based on numbers
Get Excel *.xlsx file

1. COUNT Function Syntax

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

2. COUNT Function Arguments

value1	Required. A cell reference, array, or constants for which you want to count numbers.
[value2]	Optional. Up to 255 additional arguments.

3. COUNT function - example

Formula in cell D3:

=COUNT(B3:B8)

3.1 Explaining formula

COUNT(B3:B8)

becomes

COUNT({#DIV/0!;90;"B";122;TRUE;-67})

and returns 3. There are three numbers in cell range B3:B8, they are 90, 122, and -67.

4. COUNT function - count numbers in an array

COUNT function array

The COUNT function works fine with constants in an array as well.

Formula in cell B3:

=COUNT({1,"A",-5})

There are two numbers in {1,"A",-5}, they are 1 and -5.

5. COUNT function - count digits in a cell

COUNT function count digits in a cell

Formula in cell D3:

=COUNT(MID(B3,SEQUENCE(LEN(B3)),1)*1)

5.1 Explaining formula

Step 1 - Count characters in cell

The LEN function returns the number of characters in a cell value.

LEN(text)

LEN(B3)

becomes

LEN("A45BV23XC2")

and returns 10. There are ten characters in cell B3.

Step 2 - Create numbers from 1 to n

The SEQUENCE function creates a list of sequential numbers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(10)

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}.

Step 3 - Split characters into an array

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(B3,SEQUENCE(LEN(B3)),1)

becomes

MID(B3,{1; 2; 3; 4; 5; 6; 7; 8; 9; 10},1)

becomes

MID("A45BV23XC2",{1; 2; 3; 4; 5; 6; 7; 8; 9; 10},1)

and returns

{"A"; "4"; "5"; "B"; "V"; "2"; "3"; "X"; "C"; "2"}.

Step 4 - Convert text numbers to numbers

The asterisk lets you multiply numbers in an Excel formula, it also lets you convert "text" numbers to regular numbers.

MID(B3,SEQUENCE(LEN(B3)),1)*1

becomes

{"A"; "4"; "5"; "B"; "V"; "2"; "3"; "X"; "C"; "2"}*1

and returns

{#VALUE!; 4; 5; #VALUE!; #VALUE!; 2; 3; #VALUE!; #VALUE!; 2}.

Note the numbers are not enclosed by double quotes any more. The text values are now error values but that changes nothing, the COUNT function ignores error values.

Step 5 - Count numbers in array

COUNT(MID(B3,SEQUENCE(LEN(B3)),1)*1)

becomes

COUNT({#VALUE!; 4; 5; #VALUE!; #VALUE!; 2; 3; #VALUE!; #VALUE!; 2})

and returns 5.

COUNT function count digits1

6. COUNT function - count digits in a cell range

COUNT function count digits in a cell range

Formula in cell D3:

=COUNT(MID(TEXTJOIN(, TRUE, B3:B13), SEQUENCE(LEN(TEXTJOIN(, TRUE, B3:B13))), 1)*1)

6.1 Explaining formula

Step 1 - Join values in cell range B3:B13

The TEXTJOIN function combines text strings from multiple cell ranges and also delimiting characters.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(, TRUE, B3:B13)

becomes

TEXTJOIN(, TRUE, {"KHFV9Z89Y"; "8VUKTKECVP"; "DVTMFO0J0N4"; "G86ZTG7KYF"; "8PVC2LI7LMQD61D"; "41BYHNNAGOVH7F"; "50I8PM2786D"; "U27JDBG397"; "EHD8A"; "1VRIK2"; "294RZ652J8S4UHT"})

and returns

"KHFV...UHT".

Step 2 - Count characters in string

The LEN function returns the number of characters in a cell value.

LEN(text)

LEN(TEXTJOIN(, TRUE, B3:B13))

becomes

LEN("KHFV...UHT")

and returns 116. There is a total of 116 characters in the string.

Step 3 - Create numbers from 1 to n

The SEQUENCE function creates a list of sequential numbers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(LEN(TEXTJOIN(, TRUE, B3:B13)))

becomes

SEQUENCE(116)

and returns {1;2;3;...;116}.

Step 4 - Split string

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(TEXTJOIN(, TRUE, B3:B13), SEQUENCE(LEN(TEXTJOIN(, TRUE, B3:B13))), 1)

becomes

MID("KHFV...UHT", {1;2;3;...;116}, 1)

and returns

{"K";"H";"F";...;"T"}

Step 5 - Convert text numbers to regular numbers

The asterisk lets you multiply numbers in an Excel formula, it also lets you convert "text" numbers to regular numbers.

MID(TEXTJOIN(, TRUE, B3:B13), SEQUENCE(LEN(TEXTJOIN(, TRUE, B3:B13))), 1)*1

becomes

{"K";"H";"F";...;"T"}*1

and returns

{#VALUE!;#VALUE!;#VALUE!;...;#VALUE!}

Step 6 - Count numbers

COUNT(MID(TEXTJOIN(, TRUE, B3:B13), SEQUENCE(LEN(TEXTJOIN(, TRUE, B3:B13))), 1)*1)

becomes

COUNT({#VALUE!;#VALUE!;#VALUE!;...;#VALUE!})

and returns 41. There are 41 digits in cell range B3:B13.

7. COUNT function - count negative numbers

COUNT function count negative numbers

Formula in cell D3:

=COUNTIF(B3:B8, "<0")

7.1 Explaining formula

The COUNTIF function calculates the number of cells that meet a condition.

COUNTIF(range, criteria)

COUNTIF(B3:B8, "<0")

becomes

COUNTIF({#DIV/0!;90;"B";122;TRUE;-67}, "<0")

and returns 1. -67 is the only negative number in the array.

8. COUNT function - count positive numbers

COUNT function count positive numbers

Formula in cell D3:

=COUNTIF(B3:B8, ">0")

8.1 Explaining formula

The COUNTIF function calculates the number of cells that meet a condition.

COUNTIF(range, criteria)

COUNTIF(B3:B8, ">0")

becomes

COUNTIF({#DIV/0!;90;"B";122;TRUE;-67}, ">0")

and returns 2. 90 and 122 are larger than 0 (zero).

9. COUNT function - count numbers in a given range

COUNT function count numbers in range

Formula in cell D3:

=COUNTIFS(B3:B8, ">80",B3:B8, "<150")

9.1 Explaining formula

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

COUNTIFS(B3:B8, ">80",B3:B8, "<150")

becomes

COUNTIFS({#DIV/0!;90;"B";122;TRUE;-67}, ">80",{#DIV/0!;90;"B";122;TRUE;-67}, "<150")

and returns 2. 90 and 122 are larger than 80 and smaller than 150.

10. Count numbers per row

Count numbers per row

The image above shows an Excel 365 dynamic array formula that calculates how many numbers there are on each row in cell range B3:E9.

Excel 365 formula in cell G3:

=BYROW(B3:E9,LAMBDA(a,COUNT(a)))

Explaining formula

Step 1 - Count cells only numbers

The COUNT function counts all numerical values in an argument.

Function syntax: COUNT(value1, [value2], ...)

COUNT(a)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,COUNT(a))

Step 3 - Count nonempty cells per row

The BYROW function puts values from an array into a LAMBDA function row-wise.

Function syntax: BYROW(array, lambda(array, calculation))

BYROW(B3:E9,LAMBDA(a,COUNT(a)))

11. Sort rows based on numbers

Sort rows based on numbers

Excel 365 dynamic array formula in cell G3:

=SORTBY(B3:E9,BYROW(B3:E9,LAMBDA(a,COUNT(a))),-1)

Explaining formula

This formula works just like the example in section 10, but it also sorts rows based on the count of cells containing only numbers. The values in G3:J3 has three numbers, 87, 86, and 67. That row has the largest number of numerical values in B3:E9 shown in cell K3.

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

12. Count numbers per column

Count numbers per column

The image above shows an Excel 365 dynamic array formula that counts numbers per column in cell range B3:E9.

Excel 365 formula in cell B11:

=BYCOL(B3:E9,LAMBDA(a,COUNT(a)))

Explaining formula

Step 1 - Count numbers

The COUNT function counts all numerical values in an argument.

Function syntax: COUNT(value1, [value2], ...)

COUNT(a)

Step 2 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,COUNT(a))

Step 3 - Count numbers per column

The BYCOL function passes all values in a column based on an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then continues with the next column until all columns in the array have been processed.

Function syntax: BYCOL(array, lambda(array, calculation))

BYCOL(B3:E9,LAMBDA(a,COUNT(a)))

13. Sort columns based on numbers

Sort columns based on numbers

Excel 365 dynamic array formula in cell G3:

=SORTBY(B3:E9,BYCOL(B3:E9,LAMBDA(a,COUNT(a))),-1)

Explaining formula

This formula works just like the example in section 12, however, it also sorts the columns based on the number of cells containing only numbers.

The image above shows 5 in cell G11, and there are five numbers cells in G3:G9. The next cell H11 displays 4, there are four cells in H3:H9 containing only numbers.

The SORTBY function sorts a cell range or array based on values in a corresponding range or array.

Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)

'COUNT' function examples

Convert dates into date ranges
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

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 […]

More examples

Functions in this article

COUNT MID SEQUENCE LEN TEXTJOIN TRUE COUNTIF COUNTIFS BYROW LAMBDA SORTBY BYCOL

Functions in 'Statistical' category

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

AVEDEV function

Calculates the average of the absolute deviations of data points from their mean.

AVERAGE function

Calculates the average of numbers in a cell range.

AVERAGEA function

Returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.

AVERAGEIF function

Returns the average of cell values that are valid for a given condition.

AVERAGEIFS function

Returns the average of cell values that evaluates to TRUE for multiple criteria.

BETA.DIST function

Calculates the beta distribution.

BETA.INV function

Calculates the inverse of the cumulative beta distribution.

BINOM.DIST function

Calculates the individual term binomial distribution probability.

BINOM.INV function

Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.

CHISQ.DIST function

Calculates the probability of the chi-squared distribution, cumulative distribution or probability density.

CHISQ.DIST.RT function

Calculates the right-tailed probability of the chi-squared distribution.

CHISQ.INV function

Calculates the inverse of the left-tailed probability of the chi-squared distribution.

CHISQ.INV.RT function

Calculates the inverse of the right-tailed probability of the chi-squared distribution.

CHISQ.TEST function

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.

CONFIDENCE.NORM function

Calculates the confidence interval for a population mean.

CONFIDENCE.T function

Calculates the confidence range for a population mean using a Student's t distribution.

CORREL function

Calculates the correlation between two groups of numbers.

COUNT function

Counts all numerical values in an argument.

COUNTA function

Counts the non-empty or non-blank cells in a cell range.

COUNTBLANK function

Counts empty or blank cells in a range.

COUNTIF function

Calculates the number of cells that is equal to a condition.

COUNTIFS function

Calculates the number of cells across multiple ranges that equals all given conditions.

COVARIANCE.P function

Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.

COVARIANCE.S function

Calculates the sample covariance meaning the average of the products of deviations for each pair in two different datasets.

EXPON.DIST function

Calculates the exponential distribution representing an outcome in the form of probability.

F.DIST function

Calculates the F probability for two tests.

F.DIST.RT function

Calculates the right-tailed F probability for two tests.

F.TEST function

Calculates the two-tailed probability from an F-test

FORECAST.LINEAR function

Calculates a value based on existing x and y values using linear regression.

FREQUENCY function

Calculates how often values occur within a range of values and then returns a vertical array of numbers.

GAMMA function

Calculates the GAMMA value.

GAMMA.DIST function

Calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution.

GEOMEAN function

Calculates the geometric mean.

GROWTH function

Returns estimated exponential growth based on given data.

HARMEAN function

Calculates the harmonic mean.

HYPGEOM.DIST function

INTERCEPT function

Returns a value representing the y-value where a line intersects the y-axis.

KURT function

Calculates the kurtosis.

LARGE function

Calculates the k-th largest value from an array of numbers.

LINEST function

Returns an array of values representing the parameters of a straight line based on the "least squares" method.

LOGEST function

Returns an array of values representing the parameters of an exponential curve that fits your data, based on the "least squares" method.

LOGNORM.DIST function

Calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.

MAX function

Calculate the largest number in a cell range.

MAXIFS function

Calculates the highest value based on a condition or criteria.

MEDIAN function

Calculates the median based on a group of numbers. The median is the middle number of a group of numbers.

MIN function

Returns the smallest number in a cell range.

MINA function

Returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).

MINIFS function

Calculates the smallest value based on a given set of criteria.

MODE.MULT function

Returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.

MODE.SNGL function

Calculates the most frequent value in an array or range of data.

NORM.DIST function

Calculates the normal distribution for a given mean and standard deviation.

NORM.INV function

Calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.

PERCENTRANK.EXC function

Calculates the percent rank of a given number in a data set.

PERCENTRANK.INC function

Calculates the percent rank of a given number compared to the whole data set.

PERMUT function

Returns the number of permutations for a set of elements that can be selected from a larger number of elements.

PERMUTATIONA function

Returns the number of permutations for a specific number of elements that can be selected from a larger group of elements.

PHI function

Calculates a number of the density function for a standard normal distribution.

PROB function

Calculates the probability that values in a range are between a given lower and upper limit.

QUARTILE.EXC function

Returns the quartile of a data set.

QUARTILE.INC function

Returns the quartile of a data set, based on percentile values from 0..1, inclusive.

RANK.AVG function

Returns the rank of a number out of a list of numbers.

RANK.EQ function

Calculates the rank of a number in a list of numbers, based on its position if the list were sorted.

SKEW function

Calculates the skewness of a group of values.

SLOPE function

Calculates the slope of the linear regression line through coordinates.

SMALL function

Returns the k-th smallest value from a group of numbers.

STANDARDIZE function

Calculates a normalized value from a distribution characterized by mean and standard_dev.

STDEV.P function

Returns standard deviation based on the entire population.

STDEV.S function

Returns standard deviation based on a sample of the entire population.

STDEVA function

Estimates the standard deviation from a sample of values.

STDEVPA function

Returns the standard deviation based on the entire population, including text and logical values.

TREND function

Calculates values along a linear trend.

TRIMMEAN function

Calculates the mean of the interior of a data set.

VAR.P function

Returns the variance based on the entire population. The function ignores logical and text values.

VAR.S function

The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the COUNT function

Table of Contents

1. COUNT Function Syntax

2. COUNT Function Arguments

3. COUNT function - example

3.1 Explaining formula

4. COUNT function - count numbers in an array

5. COUNT function - count digits in a cell

5.1 Explaining formula

Step 1 - Count characters in cell

Step 2 - Create numbers from 1 to n

Step 3 - Split characters into an array

Step 4 - Convert text numbers to numbers

Step 5 - Count numbers in array

6. COUNT function - count digits in a cell range

6.1 Explaining formula

Step 1 - Join values in cell range B3:B13

Step 2 - Count characters in string

Step 3 - Create numbers from 1 to n

Step 4 - Split string

Step 5 - Convert text numbers to regular numbers

Step 6 - Count numbers

7. COUNT function - count negative numbers

7.1 Explaining formula

8. COUNT function - count positive numbers

8.1 Explaining formula

9. COUNT function - count numbers in a given range

9.1 Explaining formula

10. Count numbers per row

Explaining formula

Step 1 - Count cells only numbers

Step 2 - Build the LAMBDA function

Step 3 - Count nonempty cells per row

11. Sort rows based on numbers

Explaining formula

12. Count numbers per column

Explaining formula

Step 1 - Count numbers

Step 2 - Build the LAMBDA function

Step 3 - Count numbers per column

13. Sort columns based on numbers

Explaining formula

'COUNT' function examples

Functions in this article

Functions in 'Statistical' category

Excel function categories

Excel categories

Leave a Reply

How to comment