Excel COUNTA Function

The Excel COUNTA function returns the number of non-empty cells from a specified range

Example: Excel COUNTA Function

Excel COUNTA Function

METHOD 1. Excel COUNTA Function

EXCEL

=COUNTA(C5:C13)
Result in cell C14 (8) - returns the number of non-empty cells from the selected range.

=COUNTA(D5:D13)
Result in cell D14 (7) - returns the number of non-empty cells from the selected range.

METHOD 2. Excel COUNTA function using the Excel built-in function library

EXCEL

Formulas tab > Function Library group > More Functions > Statistical > COUNTA > populate the input box

=COUNTA(C5:C13)
Note: in this example we are populating an input box with a single range.
Built-in Excel COUNTA Function

METHOD 1. Excel COUNTA function using VBA

VBA

Sub Excel_COUNTA_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("COUNTA")
'apply the Excel COUNTA function
ws.Range("C14") = Application.WorksheetFunction.CountA(ws.Range("C5:C13"))
ws.Range("D14") = Application.WorksheetFunction.CountA(ws.Range("D5:D13"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named COUNTA.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("C14") and ("D14") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.

Usage of the Excel COUNTA function and formula syntax

EXPLANATION

DESCRIPTION
The Excel COUNTA function returns the number of non-empty cells from a specified range.
SYNTAX
=COUNTA(value1, [value2], ...)
ARGUMENT(S)
value1: (Required) Any value, a cell reference or a range of cells.
value2: (Optional) Any value, a cell reference or a range of cells.

ADDITIONAL NOTES
Note 1: In Excel 2007 and later the COUNTA function can accept up to 255 value arguments. In Excel 2003 the COUNTA function can only accept up to 30 value arguments.
Note 2: The COUNTA function ignores empty cells.