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

### 'COUNT' Function examples

The following 9 articles have formulas containing the COUNT function.

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

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

The formula in cell C3 grabs the first word in B3 using a blank as the delimiting character. =LEFT(B3,SEARCH(" ",B3)-1) […]

The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]

This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]

This article demonstrates two ways to calculate expenses evenly split across multiple people. The first one is a formula solution, […]

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