### COUNTA() vs =Count()

Last week a reader asked how to count the number of non blank cells in a column of data. Well there are two easy ways to do this in Excel depending on the data types in the cells.

Here is a simple list of items and descriptions in a worksheet. Notice that in column A all the values are numeric or the cell is empty. In column B all the values are text or the cell is empty.

 A B 1 Item # Description 2 1297 shoe 3 1306 glove 4 1240 hat 5 6 1420 pants 7 1374 shirt 8 1322 tie 9 10 1038 sock 11 12 1212 sweater 13 1001 vest 14 1379 jacket 15 1003 coat

Using =Count(range) we can count the number of cells in a row that have numbers and are not blank. If we look at column A of the above data,  I can use the formula =COUNT(A2:A15) to get my answer of 11. However if I attempt to use the same formula in column B, I get a very different result. =COUNT(B2:B15) returns 0.

That’s because Count() works with numbers. To count the number of cells in column B that are not blank I need to use =CountA(range). =COUNTA(B2:B15) = 11. =CountA(range) will count the number of cells that have any value in them (number or text).

If I have a mixture of cells (number and text) I can use a combination of =Count() and =CountA() to get my answer. Let’s look at the column B data with a mixture of text and numbers.

 Description shoe glove hat 15 pants shirt tie 17 sock 19 sweater vest jacket coat

If I want to know how many cells have text values I would need to use the formula
=CountA(range) – Count(range) to find my result.

Since =CountA(range) counts all non blank cells I need to subtract the number of cells with only numbers to get my result.

=COUNTA(B2:B15)-COUNT(B2:B15) = 11

If I want to know how many cells have number values in column B I can still use the formula =COUNT(B2:B15) to get my answer of 3.