COUNT and COUNTA Functions

The COUNT function in Google Sheets allows you to count a range of cells that contain numeric values. Numeric values can include any number types ranging from standard values, monetary amounts, and percentages, as well as dates, times and durations. Pretty much any cell that has a number in it can be counted; however, there is a limitation, if a cell contains any plain text or plain text with numbers, then the count will ignore it.

In cases where you mix numbers and text, you’ll need to use the COUNTA function instead.

COUNTA counts all values in a data set, including cells containing a formula, even when no value is visibly present in the cell. But we’ll cover this later.

As mentioned before, COUNT only counts numeric values; text values are ignored, so let's look at an example.

Below, we have various numeric values underneath the title ‘Count & Counta Function’.

COUNT

In the example above, I have set up the first formula for a COUNT, which uses the following formula:

=COUNT(B2:B12)

This formula gives us a result of 10. If you count the numeric values yourself, you’ll agree there are 10 of them. So far, so good, but look more closely at the range I am using; You can see I start the range from B2, which includes the column title - ‘Count & Counta Function’. Even though this B2 is included, the count function ignores it because it contains the text.

COUNTA

Beneath the COUNT function is the COUNTA function, which uses the same range as the COUNT function but returns a count of 11 in the results. The reason is that COUNTA will count anything, so the title is included along with all the numeric values.

Counting Blank Cells that contain a formula

Let’s look at a case where a cell appears blank, yet it’s counted regardless. In the example below, I’ve made one small change in the B12 cell.

So now we have 9 numeric values for the COUNT function, but the COUNTA still shows 11 in the results even though there are only 10 visible items to count.

So what’s going on! As far as Google Sheets is concerned, this B12 cell contains some value, even though nothing can be seen in the cell itself. If you look at the top, you can see the formula, and Google counts it as part of this COUNTA function because, effectively, in the function's eyes, it’s classed as a zero-length string.

If you wondered, this function checks if the cell above equals 100; if it does, the IF statement tells it to output a zero-length string - this is the double quote “” within the formula. The 0.8 that follows it will be shown if the cell above did not equal 100. Because this cell is formatted as a percentage, it displays 80.00% when the B11 cell does not equal 100.

Now you can see I have changed the value from £100 to £99, and we can see the 80.00%; consequently, the COUNT function can see this as a value bringing the result back to 10.

The power of COUNTA

Most people will just use the COUNT and COUNTA functions in isolation to count how many rows or columns contain something, which is nice for the most part, but COUNT and COUNTA can do so much more when used in conjunction with other formulas.

Take this formula, for example; it returns the last row in the range thanks to the COUNTA function. The last row was the 80.00% value we covered earlier, and we can easily collect it when used with an INDEX function.

In case you’re wondering, the INDEX function returns the content of a cell specified by a row and column offset. The function requires a range referred to as a reference with optional arguments for row and column in that order.

=INDEX(reference, [row], [column])

We don’t need to provide any arguments ([row], [column]) for it to work, but without even a row, it will return everything in the range. I just specified a row and ignored the column, as it’s optional. I could have written the formula out like this to get the same result:

=INDEX(B2:B12,11)

Basically, I’m asking the index to return row 11 in the range, but with the COUNTA thrown in, it makes this formula dynamic since the COUNTA will do the counting for us and always returns the last row even if more data is added at the bottom later.

This is just one example of many for the COUNT and COUNTA functions, but you get the idea at least; it’s a versatile function.

Have a go yourself with the Google Sheet document used in this example

Happy Counting!