• Excel Book Excerpt

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

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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?

Figure 274 Why does Excel think the count is two?

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

Figure 275 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.

Figure 276 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.

Figure 277 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.

Figure 278 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.

For more resources for Microsoft Excel: