Excel COUNTA Function

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

Excel COUNTA function in the formula bar showing syntax.

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.


1. COUNTA covering all types

Example of an Excel COUNTA function showing some cells filled with number or text string.

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.

An example of what pressing and holding Ctrl then the G key looks like when you want to select empty cells in a range.