# How to use the COUNT function

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

Formula in cell D3:

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

#### Table of Contents

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

### 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:

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:

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

## 6. COUNT function - count digits in a cell range

Formula in cell D3:

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

Formula in cell D3:

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

Formula in cell D3:

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

Formula in cell D3:

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

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:

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

Excel 365 dynamic array formula in cell G3:

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

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:

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

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],â€¦)

### 'COUNT' function examples

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

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

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

### Functions in this article

### Functions in 'Statistical' category

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

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse 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 OscarYou can contact me through this contact form