Excel: The Count Option of the AutoSum Dropdown Doesn't Appear to Work

Problem: I am using the Count option from the AutoSum dropdown on the toolbar, but it does not appear to provide consistent results. Cells B11 and C11 both contain counts of the cells in rows 2 through 10 of each column. One function indicates that there are nine entries; the other function indicates that there are only two. Clearly, both columns have nine entries. What is the problem?

Strategy: The COUNT function will count only numeric entries. If you need to count all entries, you have to use the COUNTA function. One solution is to edit the formula in B2 by adding an A after the T in COUNT. The other method is to enter the formula correctly in the first place. Here's what you do:

1. Why does Excel think the count is two?
   1. Put the cell pointer in B11. Select AutoSum dropdown, More Functions. There are hundreds of functions available, and it can be difficult to remember where a function is; for example, you don't know if COUNTA is in the Math & Trig section or somewhere else.

   

2. The AutoSum dropdown can lead to more functions.
   2. In the Search for a Function box, type the words "œcount text" then click Go. Excel will propose possible functions. You can click on each function to see a one-line description of what the function does.

   

3. Excel proposes functions related to your search.
   3. Click on COUNTA and then click OK. Excel will analyze your data and predict the range that you want to use. However, Excel is not good at predicting data when the range contains numeric and alphanumeric entries. The Function Arguments dialog box appears. In this particular case, Excel assumes that you only want to use COUNTA on the range B9:B10.

   

4. Excel guessed the range incorrectly.
   4. If you can see the data on the worksheet, use the mouse and highlight the correct range. If the range is behind the dialog, click the Reference icon at the right edge of the text box. Then highlight the correct range. Alternatively, you can drag the dialog box until your range is completely visible.
   5. Click OK in the Function Arguments dialog to accept the formula.

   Results: The COUNTA function returns the desired value.

   

5. COUNTA returns the expected result.

   Additional Details: COUNTA will not count blank cells. You use COUNTBLANK to return the number of empty cells in a range.