How to Count Cells with Only Numbers
This article explains how to count the number of cells in a specified range that contain only numeric values in Excel. It also describes an easy method to count cells that contain non-numeric values.
Steps
- Select the range where you want to count numeric cells (e.g., A2:A10).
- In the cell where you want to display the count of numeric cells (e.g., B1), enter the formula.
- In the cell where you want to display the count of non-numeric cells (e.g., C1), enter the formula.
Explanation of the Formulas
In Excel, you can use the COUNT function to count numeric cells in a specified range. To count non-numeric cells, subtract the count of numeric cells from the total count of all cells in the range.
Formula to Count Numeric Cells
To count the number of cells containing only numeric values in a specified range, use the following formula:
=COUNT(A2:A10)
COUNT(A2:A10) returns the number of cells containing numeric values in the range A2:A10. Text and blank cells (like A9) are ignored, counting only numeric values.
Formula to Count Non-Numeric Cells
To count non-numeric cells, subtract the count of numeric cells from the total number of non-blank cells in the range using this formula:
=COUNTA(A2:A10) – COUNT(A2:A10)
COUNTA(A2:A10) counts all non-blank cells in the range A2:A10, and COUNT(A2:A10) subtracts the numeric cell count to give the count of non-numeric cells.
Example
In the table below, column A contains data, and B1 displays the count of numeric cells, while C1 displays the count of non-numeric cells.
| A | B | C | |
|---|---|---|---|
| 1 | Data | Number of Numeric Cells | Number of Non-Numeric Cells |
| 2 | 100 | =COUNT(A2:A10) | =COUNTA(A2:A10) – COUNT(A2:A10) |
| 3 | ABC | ||
| 4 | 50 | ||
| 5 | No Data | ||
| 6 | 300 | ||
| 7 | 150 | ||
| 8 | DEF | ||
| 9 | |||
| 10 | 200 |
Results
- Number of numeric cells (B1): 5
- Number of non-numeric cells (C1): 3 (=8-5)
Summary
To count cells with only numeric values in a specified range in Excel, use the COUNT function. To count non-numeric cells, subtract the numeric cell count from the total number of cells using the COUNTA function. This method is effective and simple for data analysis tasks.