1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel COUNTA Function

Count Non-Blank Cells in Excel


Excel's COUNTA Function

Excel's COUNTA Function

© Ted French

COUNTA Function Overview

Excel’s COUNTA function is one of a group of Count Functions” that can be used to count the number of cells in a selected range that contain data including text, numbers, dates, formulas, and error values.

The function ignores blank or empty cells. If data is later added to an empty cell the function will be automatically updated to include the addition.

COUNTA's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments.

The syntax for the COUNTA function is:

= COUNTA ( Value1, Value2, ... Value255 )

Value - (Value1 is required, additional values to a maximum of 255 are optional) The list of values to be counted.

Values can be numbers, formulas, text cell references to the location of the data in the worksheet, or named ranges.

Example: Counting cells containing data with COUNTA

  1. Enter the following data into cells D1 to D4: A , 25 , 4/4/2010, D

  2. Leave cell D5 blank

  3. Enter the following data into cell D6: 34

  4. Click on cell D7 - the location where the results will be displayed

  5. Click on the Formulas tab

  6. Choose More Functions > Statistical from the ribbon to open the function drop down list

  7. Click on COUNTA in the list to bring up the function's dialog box

  8. Highlight cells D1 to D6 to enter the range into the dialog box

  9. Click OK to complete the function and close the dialog box

  10. The answer 5 should appear in cell D7 because only five of the six cells in the range contain data - cell D5 is empty

  11. When you click on cell D7 the complete function = COUNTA ( D1 : D6 ) appears in the formula bar above the worksheet

Modifying the Example's Results

  1. Click on cell D5

  2. Type a comma ( , )

  3. Press the Enter key on the keyboard

  4. The answer in cell D7 should change to 6 since cell D5 is no longer empty

  5. Delete the contents of cell D3 and the answer in cell D7 should change back to 5

©2014 About.com. All rights reserved.