Please note javascript is required for full website functionality.
MVP

Blog

A to Z of Excel Functions: the COUNTA Function

18 December 2017

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the COUNTA function.

 

The COUNTA function

This is neither the Italian COUNT function nor an Excel calculator dedicated to tallying the number of times the letter “a” occurs in a spreadsheet.  This function actually counts the number of cells that are not empty in a range.  I suppose COUNTNOTBLANK is too clunky – but it’s not quite right either (see below).

The COUNTA function employs the following syntax to operate:

COUNTA(value1, [value2], ...)

The COUNTA function has the following arguments:

  • value1: this is required and represents the first item, cell reference or range within which you want to count numbers
  • value2: second and subsequent arguments are optional.  Up to 255 additional items, cell references or ranges may be specified to count numbers.

It should be further noted that:

  • The COUNTA function counts cells containing any type of information, including error values and empty text ("").  For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value.  This is different behaviour to the COUNTBLANK function which deems empty text to be blank, i.e. COUNTA(range) + COUNTBLANK(range) does not necessarily equal the number of cells in the range, which many modellers assume to be an identity
  • The COUNTA function does not count empty cells
  • If you do not need to count logical values, text or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function
  • If you want to count only cells that meet certain criteria, use the COUNTIF or COUNTIFS functions depending upon how many criteria are required.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.

A full page of the function articles can be found here

Newsletter