How to Count Nonblank Cells in Excel Using the COUNTA Function
Question details
The user needs a formula to accurately count the total number of non-empty cells, such as names or text entries, listed vertically in a spreadsheet column.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking the number of recorded items, names, or general data entries listed in a dataset without having to manually count each cell.
- Observed behavior
- A single formula is required to automatically calculate and return the total count of all cells containing any type of data within a specified range.
Identify the exact range of cells containing the data you want to count (for example, cells A2 through A100), and click on an empty cell where you want the final count to be displayed.
Use the COUNTA Function to Count Nonblank Cells
The COUNTA function is the most direct method for counting cells that are not empty, regardless of whether they contain text, numbers, or formula errors.
Unlike the standard COUNT function, which only tallies numerical values, the COUNTA function evaluates every cell in a defined range and counts all instances of data. This makes it ideal for counting lists of names, alphabetical categories, or mixed datasets.
Click on an empty cell in your worksheet where you want the total count to appear.
Type =COUNTA( in the cell or the formula bar located at the top of the spreadsheet.
Highlight the range of cells you wish to count using your mouse, or manually type the range (for example, A2:A100).
Type the closing parenthesis ) and press the Enter key. The cell will immediately display the total number of nonblank entries in that range.
Easily Manage Formulas and Count Data with WPS Office
WPS Spreadsheet fully supports all standard Excel functions, including COUNTA. You can quickly analyze your datasets, count nonblank cells, and perform complex calculations in a highly intuitive and familiar interface.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the document containing the data you want to count.
- 2. Select a blank cell: Click on the specific cell where you want the final count result to be displayed.
- 3. Input the COUNTA function: Type =COUNTA( and select your desired column or data range by dragging your cursor over the cells.
- 4. Get the result: Press the Enter key to instantly display the count of all non-empty cells in the selected area.
Frequently Asked Questions
What is the difference between the COUNT and COUNTA functions?
The COUNT function only tallies cells that contain strictly numeric values (numbers, dates, or times). The COUNTA function is more comprehensive and counts any cell that is not empty, including text strings, names, logical values, and symbols.
Why is COUNTA counting cells that look completely blank?
If a cell contains a hidden space character, an invisible apostrophe, or a formula that returns an empty string (""), COUNTA treats it as containing data. To fix this, you can click on the supposedly blank cell and press Delete, or use the TRIM function to remove extra spaces.
How do I count only the explicitly empty cells in a range?
To count the number of completely empty cells rather than the filled ones, use the COUNTBLANK function instead. For example, typing =COUNTBLANK(A2:A100) will return the total number of blank cells within that specified range.
Can I count nonblank cells across multiple separate columns at once?
Yes, you can use COUNTA to evaluate multiple non-contiguous ranges within a single formula. Simply separate the different ranges with commas, for example: =COUNTA(A2:A10, C2:C10).
