Excel: The COUNT and COUNTA functions


Excel COUNT and COUNTA function: YOUR OBJECTIVE

You are using Excel to compile a list in which you want to keep track of how many entries have been made in a certain column. To determine how many cells within a defined range contain content, you can use the excel  COUNT and COUNTA functions.

In this learning module, you will find out how to use the excel COUNT and COUNTA functions.

 

Excel COUNT and COUNTA function:HOW DOES IT WORK?

Excel has two functions that allow you to determine how many cells in a range of cells have content:

Within a given range of cells, the excel COUNT function counts the number of cells that contain a number.

In contrast, COUNTA counts how many cells are not entirely empty, without regard for the nature of the contents – numbers, dates, text or space characters.

CAUTION

The function COUNTA is a function that can only return correct results if you have not “cleared” the content of cells by entering a space character. Even though you cannot see a space character, COUNTA sees the space character and counts it as content.

In the following example, the excelCOUNT function will be used to count the number of seats in column A, rows 5 to 24. This will show the total number of available seats. In column B, the function COUNTA will be used to count how many names were entered (i.e., any non-empty cells).

 

To use the excel COUNT function, first click on the cell that is to contain the results, and then click the small black arrow on the button (Home tab, Editing group). 

 

Then select Count Numbers from the list.

 

In this example, because there are no cells with numbers immediately adjacent to the results cell, Excel does not suggest a range of cells: 

 

If Excel makes no range suggestion, as in the present case, or if the suggestion made by Excel does not meet your requirements, select the desired range of cells now. Then press [Enter]. The results cell (here, F4) now indicates how many cells in the range A5 to A24 contain a number. Cells with text (those containing “x”) are not counted: 

 

To count the number of cells filled with text as well as numbers, use the function COUNTA. Although COUNTA cannot be accessed via the button, you can simply select the excel COUNT function with this button and then insert the missing A manually:

First click the cell that is to contain the result. Then click the small arrow on the  button (Home tab, Editing group). 

 

Select Count Numbers from the list

 

Now select the range of cells in which you want this function to operate (in this example, B5 to B24). Then press [Enter]. As you can see, since there are no numbers in the range, the COUNT function doesn’t count anything and returns a 0.

 

You actually need to use COUNTA. To change the excel COUNT function to a COUNTA function, click just before the opening parenthesis and enter an “A”. Then press [Enter]. The COUNTA function now shows the number of all non-empty entries in the column B range. 

 

HINT

Of course, you can also type in either of these functions manually. To do so, after entering “=”, type the name of the function (COUNT or COUNTA) and then enter a left parenthesis ( “(” ). Do not type a space between the function name and the parenthesis! Now you can either type in the range of cells (e.g., B5:B24) or use your mouse to select them. As soon as you release the mouse button, the selected range of cells is transferred to the function. If you now press [Enter], the function will be accepted and the result appears in the cell.

HINT

Functions that cannot be accessed via the button can be selected with help from the function assistant, which is discussed in detail in the Function assistant learning module.

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen