Excel COUNTA: A Step-by-Step Tutorial to Show You How to Use COUNTA

With over a billion copies of Microsoft Office sold, Excel is currently the most widely used spreadsheet application in the world and with good reason too. Microsoft Excel offers the power of over 400 built-in functions and for those who want to design their own customs functions, Excel offers the versatility and power of VBA for applications as well. To learn to harness the power of MS Excel in your daily tasks, sign up for the Microsoft Excel 2013 Course Beginners/ Intermediate Training from Udemy today.

This tutorial will show you how to use the COUNTA function in your worksheets to calculate the number of data values there are in a specific range where the range may contain blank cells. We have set up a fictitious set of data for this tutorial to show you how to apply the COUNTA function in your worksheet.

The data we will use contains Jack’s client details. Details include the client name, Email address, Address, Telephone and Sales Amount for each client, but the data does not include all items for each column, so Jack may only have a client name and address without the rest of the data for a particular client. In other words, data is missing for some of the columns and rows.

This is the data we will use for our tutorial examples:

If you would like some tips and tricks on working with data, you can read Excel Tips and Tricks: Use Excel like a Pro on the Udemy blog.

How to Use Functions in Excel

Excel allows you to use functions by typing the function directly into the target cell – i.e. the cell you want the answer to appear in, or by selecting the function from the formula tab. The advantage of using the formula tab to include functions in your spreadsheet, is that if you select the function, Excel automatically opens a function wizard that guides you through the parameters required by that particular function.

For the purposes of this tutorial, we will use the formula tab to add the COUNTA function to our worksheet. For more information on functions, read Excel Formulas: 10 Formulas That Helped Me Keep My Job.

To use any function, you need to know what the function does and how to specify the parameters of that function. To understand what parameters the function requires, you need to know the syntax of the function.

The COUNTA function in Excel returns a value that represents the number of cells within a range that contains data. In other words, it counts the number of cells that contain data.

The syntax for the COUNTA function can be expressed as follows:

=COUNTA( value1,[value2], …)

The COUNTA function is initialized by typing COUNTA in the target cell or by selecting COUNTA from the formula menu. The value1 represents the first range or set of data you want to count. You can include up to 255 sets of data to count in the COUNTA argument.

To learn the true power of formulas in your own worksheets, sign up for the Mastering Excel with MS-2007, 2010, 2013 course available from Udemy today.

Let’s use COUNTA in our worksheet to illustrate how you can use COUNTA.

How many Email Addresses Does Jack Have?

Our first example will show you how to count the cells that contain data in a specific range. We will use COUNTA to count how many email addresses Jack’s client database contains.

To use the COUNTA function in our worksheet, click on the target cell where you want the answer to appear, then select the formula tab from the menu and then select COUNTA from the More Functions, Statistical tab.

To search the email column select the email range using your mouse or type in C5:C17 to select the range that contains email addresses. Your formula wizard will look like this:

And the result will look like this:

Named Ranges and COUNTA

The COUNTA function can also be used with named ranges. To learn how to create a named range and to learn the basics of Excel, sign up for the Microsoft Excel 2013 Training Tutorial course from Udemy.

To use the COUNTA function on a named range, you merely need to use the named range as the value within your COUNTA function parameters.

For the purposes of this tutorial we have now named the Email data column in our worksheet as “emails”.

This is what your function wizard will look like to use the COUNTA function on the range called “emails”:

Search Entire Columns using COUNTA

The COUNTA function also allows you to count cells that contain values within an entire column. To search and count the cells in a column, you can use the ColumnLetter:ColumnLetter without specifying a row number.

To search column C for example for cells containing data the formula wizard will look like this:

And the result will look like this:

Note that the answer this time is ten because COUNTA included one for the heading “Email”. For a great course on Excel, sign up for Use Excel Like A Pro. Fast from Udemy.

Search Entire Rows Using COUNTA

You can also search and count values within an entire row or range of rows. To search a row or rows, you can enter rowNumber:rowNumber as the search range in the formula wizard.

The result of this COUNTA function will look like this:

Using COUNTA with Other Formulas

COUNTA can also be used in conjunction with other formulas. As our last example of how to use the COUNTA function, we will work out the average sales that Jack has made. COUNTA is useful if you want to average data within a range and the range contains empty cells and you do not want the empty cells to affect the average.

To work out the average sales we need to add up all of jack’s sales and then divide that total by the number of sales in total. We will use COUNTA to calculate how many sales there were.

This is the formula you need to enter into the target cell:

And this is what the answer will be using the above formula.

To learn how to apply the functions offered by Excel, sign up for the Microsoft Excel 2013 Advanced Online Excel Training Course from Udemy today and learn to how Excel can make your spreadsheet life simpler.