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

## 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 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 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. ## 6. 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(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.

## 7. 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(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.

## 8. 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(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).

## 9. COUNT function - count numbers in a given 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.