What does it do?
The Excel COUNTA function very simply counts the number of cells in a range that are not empty. The cell(s) can contain numbers, text by direct input or generated by logical formulation. A formulated empty result (“”) would be deemed as not empty.
Syntax Explained
Value1: This is the first range of cells to count that are not empty.
Value 2 and up to Value255 (optional): As above but for a second or greater number of ranges.
Examples
1. COUNTA covering all types
The above screen shot demonstrates various types of ways a cell can be filled, or not.
The rows below are ALL deemed to be FILLED when using the COUNTA function:
- Row 3 – Filled with a text.
- Row 4 – Filled with a number (note this can be ANY number i.e. Date, whole number, fraction etc).
- Row 5 – A cell containing a SPACE, not visually obvious, so I underlined it in my example.
- Row 6 – A formula that outputs empty result, show by the red double quotes.
- Row 9 – A formula populating a result into a cell
- Row 10 – As above but an error result
The rows below are ALL deemed to be FILLED when using the COUNTA function:
- Row 7 – An actual empty cell, filled with nothing more than, well, nothing!
- Row 8 – See above
Pro Tip
You can visually highlight BLANK cells to analyse an entire sheet or range. Click anywhere in your sheet (to analyse the whole sheet), or select a range of cell (to analyse a particular area of your choice), then hold Ctrl and press G to open the GO TO window. Now click the Special… button, check the radio button next to Blanks and click OK, you should have something like the below screenshot.
