The COUNT function in Excel counts the number of cells which contain numbers. The COUNT function returns a value corresponding to the count of cells containing numbers within either selected ranges of cells, cell references, or constants. To be clear, this function does not count the sum of the numerical cells (the SUM function does this), it simply counts the number of cells that contain numbers. Read on to learn how to use the Excel COUNT function!
The COUNT function in Excel has at least one argument (function inputs inside the parenthesis separated by commas) and is written as follows:
=COUNT(value1, [value2], [value3], …)
The COUNT function requires only one argument (input) but can take up to 255 arguments. It looks at each of its arguments and returns the number of cells which contain numbers.
As mentioned above, the COUNT function returns a value corresponding to the count of cells containing numbers within the user-defined scope. Remember, it does not count or add the numbers themselves - it simply counts the number of cells which contain numbers. 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, including integers, fractions, dates, times, and percentages. It will NOT count any of the types of values from column B, including empty cells, text, and logical values.
Typically in Excel, when a number is enclosed in quotation marks, such as "4," it is considered text and not treated like a number. For example, the formula =5+"4" would return an error because it is trying to add a number and a text value, which doesn't make sense.
However, the COUNT function (which only counts numbers) treats numbers enclosed in quotation marks as numbers. For example, see the formula below:
=COUNT(1, "5", 6, "hello")The formula above would return 3. The function counts the two number values as well as the 5 inside quotations. The other argument "hello" is clearly not a number and is not counted.
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. The entries in the password row are text values and would not be counted.
As another example, assume one wanted to ensure the registration date logs and the login logs were intact and operational. In theory, the count of profiles with at least login must be equal to or less than the count of profiles that have a registration date (you can't log in if you haven't registered). One would use the following formulas:
=COUNT(B3:E3)=COUNT(B4:E4)The formulas would return values of 3 and 4, respectively. If the table was intact, then there would be four registration dates because there are four profiles that have logged in. It appears one of our entered cells is amiss!
The simplest use of the COUNT function is with the use of constants. A constant is when you enter an argument value directly in to the parenthesis rather than using a reference to a cell which contains the value. 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 doesn't care about the format in which you supply arguments; it simply counts the number of numerical values.
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, but learning how to use the COUNT function is the first step!
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.