Counting how many cells contain numbers is a basic and useful task. With this in mind, Microsoft Excel gives you a handy function to save you from counting cells manually. The function is COUNT and here’s how to use it.
You may have a column full of both numbers and text where you only want the total count of the cells with numbers. You might even want this type of count for an entire sheet or ranges of nonadjacent cells. All of this can be done with the COUNT function.
What You Can Count With COUNT
The COUNT function counts cells with numbers but also includes dates, currency, percentages, numbers enclosed in quotes, and numbers in a table or pivot table in Excel.
It does not count logical values, text, or errors containing numbers.
There are variations of the COUNT function such as COUNTIF for counting cells with certain criteria and also COUNTBLANK for empty cells. Be sure to check out our how-tos for these variations as well.
Use the COUNT Function in Excel
To get started, open your workbook and head to the sheet you want to use in Excel. Go to the cell where you want to display the result of the formula. You can then use the Count Numbers option in the Sum list or enter the formula manually.
To use the Count Numbers option, go to the Home tab. Click the Sum button in the Editing section of the ribbon and select “Count Numbers.”
This method works great for basic counts like one cell range. For more complicated situations, you can enter the formula containing the function.
The syntax for the function is
COUNT(value1, value2,...) where
value1 is required and
value2 is optional.
To start simple, you can obtain the count for cells with numbers in the range A2 through A11, using the following formula:
You can see here, each cell in our range contains a number. This gives us the total count of 10.
If that same range of cells contains text in five of the 10 cells, the formula’s result is 5.
To obtain the count for nonadjacent cell ranges A2 through A6 and B6 through B11, you would use this formula:
You can include even more cell ranges, if necessary, with the COUNT function. This formula counts the cells in A2 through A11, C2 through C11, and E2 through E11.
For other ways to obtain counts in Microsoft Excel, take a look at how to count colored cells or how to count cells with text.
|Mastering Excel Functions|
|Functions||AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH · |
MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR
|Types||Basic · Budgeting · Data Entry · Logical · Text · Time and Date|
|Explained||Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas|