Search notes:

Excel functions: count, countA, countBlank

count only counts numbers.

countA counts everything that is non empty, even error values.

countBlank seems to count the opposite set of what countA counts.

The following simple VBA program creates some test data to play with these functions:

option explicit

sub main() ' {
  '
  ' Data
  '
    cells( 3, 2).resize(1,6).value          = array(    0 ,    1 ,    2 ,      3 ,     4 ,     5 )
    cells( 4, 2).resize(1,6).value          = array("zero", "one", "two", "three", "four", "five")
    cells( 5, 2).resize(1,6).value          = array(""    , " "  , ""   , ""     , chr(9), ""    )
    cells( 6, 2).resize(1,6).value          = array("=7/0", ""   , ""   , "=na()", ""    , ""    )
  '
  ' Make data visually stand out:
  '
    cells( 3, 2).resize(4,6).interior.color = rgb(240, 210, 110)

  '
  ' Formulas
  '
    cells( 2, 9).resize(1,3).value          = array( "count"               ,   "countA"               ,  "countBlank"               )
    cells( 3, 9).resize(1,3).formulaR1C1    = array("=count(r[0]c2:r[0]c7)",  "=countA(r[0]c2:r[0]c7)", "=countBlank(r[0]c2:r[0]c7)")
    cells( 4, 9).resize(1,3).formulaR1C1    = array("=count(r[0]c2:r[0]c7)",  "=countA(r[0]c2:r[0]c7)", "=countBlank(r[0]c2:r[0]c7)")
    cells( 5, 9).resize(1,3).formulaR1C1    = array("=count(r[0]c2:r[0]c7)",  "=countA(r[0]c2:r[0]c7)", "=countBlank(r[0]c2:r[0]c7)")
    cells( 6, 9).resize(1,3).formulaR1C1    = array("=count(r[0]c2:r[0]c7)",  "=countA(r[0]c2:r[0]c7)", "=countBlank(r[0]c2:r[0]c7)")

    activeSheet.usedRange.columns.autofit

end sub ' }

Github repository about-Excel, path: /functions/count/count-countA-countBlank.bas

Whe executed, the program produces:

In the fifth row, countA() evaluates to 2 beacuse " " and chr(9) (the tabulator) are counted as non-empty.

Use countA() to count the number of elements in a list

countA() can be applied on a list in which case it counts the number of elements in this list:

=countA("foo"; 42; "baz"; 99; "bar") evaluates to 5

Excel functions: count, countA, countBlank

Use countA() to count the number of elements in a list

See also