The COUNT function in Excel returns a value corresponding to the count of cells containing numbers within either selected ranges of cells, cell references, or constants.
The COUNT function in Excel has at least one argument (values in the function separated by commas) and is written as follows:
=COUNT(value1, [value2], [value3], …)
The COUNT function can take up to 255 arguments.
As mentioned above, the COUNT function returns a value corresponding to a count of cells containing numbers within the user-defined scope. Let’s clarify what exactly the COUNT function counts:
A | B | |
---|---|---|
1 | Counted by the COUNT Function | NOT Counted by the COUNT Function |
2 | Integers ( 1, 2, 3, 4, etc. ) | Empty Cells |
3 | Decimals ( 1.2, 3.45, 6.789, etc. ) | Text Values ( “Tree”, “The Cats”, “bEaRs123”, etc. ) |
4 | Negative Values ( -1, -2, -3, etc. ) | Logical Values ( TRUE & FALSE ) |
5 | Percentages ( 100%, 200%, 35%, etc. ) | |
6 | Fractions ( 3/5, 5/3, 1 2/3, etc. ) | |
7 | Dates ( 3/17/2020, ‘March 17, 2020’, etc. ) | |
8 | Time ( 2:52, 14:52, 00:14:52:55, etc. ) |
The COUNT function will count the number of cells which contain any of the types of values from column A. It will NOT count any of the types of values from column B.
Suppose you had the following table in Excel showing user login information.
A | B | C | D | E | |
---|---|---|---|---|---|
1 | Username | Root | ADMIN | Bill_Gates | TheRealObama |
2 | Password | Password123 | Michelle765! | ||
3 | Registration Date | 07/24/2008 | 06/05/2010 | 02/04/2014 | |
4 | Number of Logins | 879 | 1567 | 12 | 71 |
If one wanted to use the COUNT function on the entire table (excluding the headers), one would use the following formula:
=COUNT(B1:E4)
The formula would return a value of 7. There are three date entries within the range, and there are four integer login entries within the range.
As another example, assume one wanted to ensure the registration date logs and the login logs were intact and operational. In theory, the returned number by the COUNT function should be the number of entered values multiplied by two. One would use the following formula:
=COUNT(B3:B4, C3:C4, D3:D4, E3:E4)
The function would return a value of 7. There were four values used in the count function. If the table was intact, it would return a value of 8. It appears one of our entered cells is amiss!
The simplest use of the COUNT function is with the use of constants. Constants can be used alongside other arguments like cell references.
A | B | |
---|---|---|
1 | State | Population (Millions) |
2 | California | 39.56 |
3 | Texas | 28.7 |
4 | Florida | 21.3 |
5 | New York | 19.54 |
6 | Illinois | 12.74 |
7 | Pennsylvania | 12.81 |
If you wanted to include constants in the COUNT function, you use them like you'd use any other argument:
=COUNT(B1:B7, 150, 2000)
The formula would return a value of 8; One for each cell from B2 to B7, and one for each constant argument.
The COUNT function outlined in this tutorial is the original iteration of functions in its family. There are many other COUNT functions used in Excel, suited for a variety of tasks.
If counting numbers only, use the COUNT function.
If counting numbers AND text, use the COUNTA function.
If counting empty cells, use the COUNTBLANK function.
If counting based on a single criterion, use the COUNTIF function.
If counting based on multiple criteria, use the COUNTIFS function.
Now that you've read about the COUNT function in Excel, try a few fun practice exercises to get some hands-on experience and internalize the function.
Try some Excel Exercises with the COUNT function now!