Skip to content

Excel COUNTA Function – Count Non Empty Cells

  • by
  • Excel

The Excel COUNTA function is a built in function that is used as a relatively simple formula to count the number of cells that are not empty – containing numbers, text, strings in general, TRUE and so on.

In this simple guide we will run through how to use the Excel COUNTA function, along with some examples. We will cover:

  • The Excel COUNTA formula
  • Examples of COUNTA
  • COUNTA vs COUNT

Let’s get started with the formula.

The Excel COUNTA Formula

The formula for this one is pretty simple. The syntax is as follows:

=COUNTA(value1,value2)

That’s all that is needed! Essentially value1 is the range of values such as a single column or row, while value2 in this case is purely optional but allows us to select a second column of data sitting elsewhere in the spreadsheet if we want to include this in a single count. We aren’t limited to just two sets of data either so can continue with value3 if need be as well.

Since we are working with ranges, an example formula with data sitting in columns B, C and D might look like the below:

=COUNTA(B2:B10,C2:C10,E2:E10)

That being said, sometimes if we have a whole lot of columns of data it might be easier to just run separate COUNTA formulas and sum the results.

Now that we have the formula sorted, let’s take a quick look at an example.

Examples of COUNTA

For this example we are going to use the below single column of data:

Excel counta function

As we can see, the number of rows our data covers (excluding the header) is 9, however we are using a mix of words, numbers and symbols, along with a couple of empty cells as well.

Using our COUNTA formula across cells B3 to B11, we are then presented with an output of 7 as shown below:

Excel counta function

The overall process for this one is pretty simple, and when working with a dataset such as the above is a quick and easy way to work out how many different values we actually have. Although that being said if we are working with a single column of data, or multiple columns that are literally side by side, if we were to just highlight all of the cells we would actually see the exact same result in the bottom corner:

Excel counta function

In our use case this is probably the easiest, unless we then wanted to use that 7 in another formula somewhere else down the line.

COUNTA vs COUNT

One final piece worth noting is that COUNTA isn’t actually the only COUNT option we have available in Excel. There are a few different types, with some being a bit more complex than others, such as COUNTIF, but the one to highlight here is simply COUNT.

The key difference between the two is simply that while COUNTA counts all of the non empty cells containing numerical values, text, symbols and so on as highlighted above, COUNT simply looks for numerical values.

If we were to use the exact same dataset above but just use COUNT then we would see the following output:

Excel count function

As we can see, the Hello, World, % and Age 55 values have all been completely excluded from the count as they are not pure numerical values.

Using COUNT instead of COUNTA in Excel can be handy as a comparison as we could then do a subtraction formula to work out how many cells don’t just contain numbers. Calculations like this are useful when working with datasets when we should just be using numbers in our columns but for some reason are not, and can provide a nice indication of how messy our data is.

This sums up our simple guide on how to use the Excel COUNTA function. For more handy guides on working with Excel, be sure to check out our Excel Tips page.