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.