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.

The other day I was asked if I could break out numbers from a cell that contained both text and numbers. My manager wanted the numbers in one column and the text to the right in the adjacent column.

I thought sure, easy to do with a combination of =Left =Mid and = Right functions. However, when I looked at the data I noticed that the numbers did not start in the same position in the text. I also noticed that some cells had 6 numbers while others had 4. To make things even more difficult, some cells started with numbers first.

Well I thought about the problem and decided to solve it by converting the formula into an array.

What’s an array formula?

An array formula is a formula that works with array data values rather than a single data value. 

There are two different types of array formulas

1.     Formulas that work with an array or series of data and aggregate the result. Sum, Average and Count are good examples of this type as they all return a single value to a single cell.

2.     Formulas that returns an array of numbers as its result. These array formulas are entered into multiple cells that are then treated as a group. For example, =ROW(A1:E20).

In the above example I have my original data in column A. I first use the formula in column B to extract the numbers in the string. After entering the formula, I hold down the control key, shift key and enter key all at the same time to convert the formula into an array.

Once I had that, it was a simple matter of getting the length of how many numbers were in the original data and the starting position of the numbers in the string (Columns C and D).

Based on that, I could then parse out the text to the right of the number (Column E).

Now to accomplish all this I needed to use 7 functions….

=IF

=MID

=MATCH

=ROW

=LEN

=COUNT

=ISNUMBER

All of these functions are simple to use but the trick was combining them to get my solution.

Indeed knowing what functions are available in Excel will allow you to join them to solve almost any problem you have to solve. So it’s a good idea to learn them or at least know they exist so that you can reference them.

{=1*MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1)))}

{=MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0)}

{=IF(MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($2:$9),1)),0) = 1,MID(A2,(E2+D2),90),MID(A2,(E2+D2+1),90))}

How would you have solved the above problem?