Author: Oscar Cronquist Article last updated on April 07, 2022

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.

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

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.

Back to top

4. COUNT function - count numbers in an array

<span class='notranslate'>COUNT</span> 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.

Back to top

5. COUNT function - count digits in a cell

<span class='notranslate'>COUNT</span> 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(textstart_numnum_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.

<span class='notranslate'>COUNT</span> function count digits1

Back to top

6. COUNT function - count digits in a cell range

<span class='notranslate'>COUNT</span> 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(delimiterignore_emptytext1[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(textstart_numnum_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

<span class='notranslate'>COUNT</span> 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(rangecriteria)

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

<span class='notranslate'>COUNT</span> 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(rangecriteria)

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

<span class='notranslate'>COUNT</span> 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_range1criteria1, [criteria_range2criteria2]…)

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