Excel Formula Help - Counting Entries and Custom Number Formatting
In long lists where table entries are irregular, we sometimes wish to know the total number of entries. Below is table of conference attendees and corresponding lists of phone numbers. We need to know the total number of attendees that showed up on the day and also the total amount of phone numbers, so that time and resources can be correctly allocated for follow up calls.
A simple =COUNTA(B3:B21) will take into account all text entries in the name column.
In a similar way, a formula to count all numeric entries, phone numbers, in the adjacent column would be =COUNT(C3:C21). However, we first need to make sure that the referenced cells C3:C23 are in the correct format. As no default format for phone numbers exists, we need to custom build our own. Select the cells and right-click, Format Cells. On the Number tab of the options box, under Category, select Custom. In the Type field, type 0############, as seen here:
This will maintain the proceeding 0 for all phone numbers up to 13 digits long as well as making the the entries 'numeric' for the sake of the COUNT function. The caveat however, is that spaces must not be used, as then the COUNT function will not record the entry. The final table should look like this:
The functions COUNT and COUNTA are used to count the number of non-blank entries in either columns, as above, or rows. More details on these functions can be found in the Microsoft Office Excel Help pages here and here respectively. For more on creating a custom number format, try here.
