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 signs
Use 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 Oscar
You can contact me through this contact form