### 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.

### Doughnut Chart / Spiral Chart

A couple of posts back I demonstrated how to create a box chart. Here is thepost.

Today I wanted to demonstrate how to display the same data as a spiral graph. What’s a spiral graph you ask? Basically it’s a doughnut chart. Let me show you… In the above example I compare 3 leaf blowers based on Satisfaction, Price and Durability. Each category and item is separate. Well for the spiral graph I want to compare each category separately. I want to compare all three blower types for Satisfaction in one graph, Price in a second and Durability in a third.

To start my spiral graph I go to my data tab. For the green leaf blower I have the following data. The first column of numbers is the results of the survey. The second column of numbers is 100 – the first column (100 – 53 = 47). I need both of these numbers (53 and 47) to create the first spiral of my graph.

 Satisfaction 53 47 Price 41 59 Durability 62 38

So I start by highlighting Satisfaction, 53 and 47. Then I choose Insert > Other Charts > Doughnut Chart

This creates my first spiral in the doughnut chart.

Now I want to change the blue of the doughnut to match the green color of the Satisfaction bar chart. I select the blue by mouse clicking on it. Now you may find that when you click on the blue Excel selects both the Blue and Red colors. No big deal, just click on the blue a second time and try again.

From the format tab, change the shape fill to be green.

Repeat the process for the red portion of the doughnut but select no fill instead of a color.
When Done I have my first section of my chart.

Now I can clean up the chart by removing the legend to the right by clicking on it and hitting the delete key.

Next I want to add the Yellow Satisfaction to my chart. I right mouse click on my green spiral and choose Select Data.

This opens the Select Data Source dialog. Click the add button. For Series name select cell A20 and for series value enter =Data!\$B\$20:\$C\$20

This adds the second spiral to the chart. I repeat the process above to change the color blue to yellow and no fill on the red portion.

Finally again I repeat the Select Data and add the red Satisfaction data.

I modify the blue color to be red and no fill the other section.

I am now left with my spiral graph.

I add the chart title “Satisfaction” by selecting Chart Title centered Above Chart from the Layout tab.

Now I can add a black edge to each color in my chart if I wish by using the Shape outline option just below the shape fill shown above.

Next I cut my chart moving it from the data tab to my Dashboard tab and resize it.

I repeat the above process for Price and Durability. When done I have displayed my results two different ways, by bar chart and by doughnut chart (spiral graph).

To add some additional detail I add text boxes for numbers and a line and circle from the Insert tab > Shapes.

How can you use the above techniques to add some wow to your next dashboard?