How to use the COUNT function

What is the COUNT function?

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

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

1. COUNT Function Syntax

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

Back to top

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.

Back to top

3. COUNT Function Example

This example demonstrates show the COUNT function works, cell range B3:B8 contains the following values: #DIV/0, 90, B, 122, TRUE, -67

1. The first value is a #DIV/0! which is an Excel error. It means that the formula tries to divide number by 0 (zero) which is impossible. The COUNT function ignores error values.
2. The second value is 90 which is  a number. This is the first number.
3. The third value is "B" which is a text value, this value is not added to the total count because it is not a numerical value.
4. The fourth value is 122 which is  a number. This is the second number in the list.
5. The fifth value is TRUE which is a boolean value meaning it is not a numerical value. This value is also ignored.
6. The sixth value is -67 which is a numerical value and is added to the total.

Formula in cell D3:

The formula in cell D3 returns 3 meaning the list in cell B3:B8 contains 3 numerical value or numbers. Use the COUNT function to count numbers in a cell range, this is great for verifying that you got the correct number of numerical values in the specified cell range.

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.

Back to top

4. COUNT function - count numbers in an array

The COUNT function works fine with constants in an array as well, in other words, the COUNT function counts numerical values or numbers in hard-coded values or constants in a formula.

The formula below shows how to hard-code values, text values must have a beginning and ending double quote or the formula will returns a #NAME! error. This means that the Excel thinks you have entered a misspelled function.

Multiple hard-coded values must be entered with beginning and ending curly brackets. The tell Excel that this is an array of values, use specific characters to delimit values by row or column. These specific charcaters are determined by your regional settings. For example, US has a comma as a column delimiter and a semicolon as a delimiter for rows.

Formula in cell B3:

The array in the formula above has three values, two numerical values and one text value. There are two numbers in {1,"A",-5}, they are 1 and -5. The formula returns 2 which represents two numerical values.

Back to top

5. COUNT function - count digits in a cell

The following example demonstrates how to count digits in a cell value. Cell B3 contains "A45BV23XC2", the formula in cell D3 splits the string into one character each returning an array containing single characters in each container.

The COUNT function counts the numerical values in the array and returns the total number of numbers. I have bolded the numerical digits in the array: {"A";"4";"5";"B";"V";"2";"3";"X";"C";"2"}

Formula in cell D3:

The numbers in the array are 4, 5, 2, 3, and 2. The total number in the array is 5 which is what the formula returns in cell D3.

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.

Back to top

6. COUNT function - count digits in a cell range

This example counts all numerical values in a given cell range, the formula in section 5 counts only numerical values in one given cell. The TEXTJOIN function joins all strings in cell range B3:B13.

The formula then splits the joined text string into an array containing a single character in each container. The COUNT function counts all containers containing a numerical value, in other words, a number.

Formula in cell D3:

The following subsection 6.1 explains in greater detail how the formula works.

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"; ... ; "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.

Back to top

7. COUNT function - count negative numbers

The COUNT function can count negative numbers in a given cell range, however, you need some additional functions or clever workarounds.

Formula in cell D3:

I recommend using the COUNTIF function, it is built to count values in a cell range based on a given condition. The formula stays small and easy to understand, just the way we like it.

The COUNTIF function lets you specify a condition in the second argument. which in this case is "<0". The < character is a comparison character that checks if the cell values in cell range B3:B8 is smaller than 0 (zero), in other words, negative values.

Cell range B3:B8 contains the following values: #DIV/0, 90, B, 122, TRUE, -67. Only one value is a negative number: -67. The formula in cell D3 returns 1 representing the number of negative values in cell range B3:B8.

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.

Back to top

8. COUNT function - count positive numbers

The image above demonstrates a formula that counts positive numerical values. Cell range B3:B8 contains the following values: #DIV/0, 90, B, 122, TRUE, -67

Only values 90 and 122 are positive numbers, the remaining are negative values, boolean value, and an error value.

Formula in cell D3:

The COUNTIF function lets you specify a condition in the second argument. which in this case is ">0". The > character is a comparison character that checks if the cell values in cell range B3:B8 are larger than 0 (zero), in other words, positive numbers.

The formula in cell D3 returns 2 representing the total number of positive numbers in cell range B3:B8.

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

Back to top

9. COUNT function - count numbers in a given range

This examples show how to count numbers between a given numerical range in a specific cell range. The COUNT function can do this as well, however, there is a function built for two or more conditions. Cell range B3:B8 contains the following values: #DIV/0, 90, B, 122, TRUE, -67.

Formula in cell D3:

The formula above uses two conditions to count the numerical values in B3:B8, they are: ">80" meaning values larger than 80 and "<150" meaning numbers smaller than 150. Both conditions must be met in order for a value to be included into the count.

The formula in cell D3 returns 2 representing the total number of values smaller than 150 and larger than 80, in cell range B3:B8.

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.

Back to top

10. 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. In other words, the formula returns an array of values in cell G3 and spills values to cells below as far as needed.

The array has values that match the rows in B3:E9, each number represents the number of numerical values for that particular row.

Excel 365 formula in cell G3:

For example, row 3 has the following values: "VATE", "RVEO", 86, and 20. Only two values are numbers so the formula returns two in cell G3.

What is special about the BYROW function is that it performs the calculation for the entire range B3:E9 which is harder but doable in earlier Excel versions using the MMULT function etc.

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

Back to top

11. Sort rows based on number count

This example builds on the example above in section 10, we discussed how to count the number of numerical values per row and demonstrated a formula.

This formula below sorts the rows in B3:E9 based on the count of numerical values. For example, row 7 in B3:E9 contains three separate numbers making it the row with the highest count.

Excel 365 dynamic array formula in cell G3:

The formula in cell G3 returns row 7 at the top and then continues with rows with a lower count. The last three rows has only one number and they are rows 6, 8, and 9 in B3:E9.

Also, the Excel 365 formula spills values below and to the right as far as needed. A #SPILL! error is shown if those destinations cells are not empty. Column K shows the count next to the sorted output.

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

Back to top

12. Count numbers per column

The image above shows an Excel 365 dynamic array formula in cell B11 that counts numbers per column in cell range B3:E9. This is similar to to the formula in section 10, however, this calculates the number of numerical values per column instead of per row.

The formula returns an array that has the same number of columns as the source data range B3:E9. This means that the formula spills values to the right as far as needed.

Excel 365 formula in cell B11:

The first column two numerical values, cell B4: 47 and cell B7: 87. The next column has only one numerical value, the remaining values are text strings, 40 is found in cell C5.

The third column contains 5 numerical values, cell D3: 86, cell D4: 60, cell D6: 14, cell D7: 86, and D8: 13. The last column has 4 numerical values in cells E3, E5, E7, and E9. They are respectively 20, 5, 67, and 64.

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.

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

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

Back to top

13. Sort columns based on number count

Excel 365 dynamic array formula in cell G3:

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

Back to top

'COUNT' function examples

Functions in 'Statistical' category

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

Excel function categories

Excel categories

Leave a Comment