MrExcel.com - Your One Stop for Excel Tips & Solutions

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

January 07, 2022 - by

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:

The COUNT function only counts numeric cells. You want to AutoCount a column of text.
Figure 260. 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.

Open the AutoSum drop-down and choose More Functions...
Figure 261. The AutoSum dropdown can lead to more functions.

  • 2. In the Search for a Function box, type the words “count” 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.

In the Insert Function, type Count Text. They suggest COUNTA. Choose COUNTA and click Insert.
Figure 262. 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.

Excel incorrectly guesses that you want to COUNTA only B9:B10. You have to specify a new range in the Function Arguments dialog.
Figure 263. 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.

The formula =COUNTA(B2:B10) in B11 now correctly counts the text and numeric entries.
Figure 264. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Joshua Hoehne on Unsplash