How to Easily use the Excel COUNT Function


What does it do?

The Excel COUNT function adds up the number of cells in a range that contain numbers as the cell value.  This therefore means it does not count up cells that contain a text string.  The values can be can be whole numbers, fractions, dates, percentages, time and currency amounts, see example number 1 below.


Syntax Explained

Excel COUNT function in the formula bar showing syntax.

Value1: This is the first number or range to count number in.

Value 2 and up to Value255 (optional): As above but for a second or greater number of ranges.


Examples

1. COUNT covering all manner of cell formats.

Example of an Excel COUNT function, counting how many cells, formatted in a range with text characters, contain numbers.

The above results under the data column are all stored as a number in Excel so it recognises that as such.  Despite the cells showing various symbols and letters, they are not stored in the cell values, only as the display values.  I have linked the Cell Value Data column to show the value only from column Data.

2. COUNT using one range.

Example of an Excel COUNT function, counting how many cells contain numbers in one range.

Above you can see the COUNT function is assessing the range and has determined that there are 6 cells which contain numbers.  Pretty simple and straightforward here, nothing complex.

3. COUNT using two or more ranges.

Example of an Excel COUNT function, counting how many cells contain numbers in two ranges.

No complex wizardry here, just expanding on the ranges to count by adding another into the formula bar.  We’ve added an additional 8 into the result, giving us a total of 14.  You can repeat the process for selecting the second range for any subsequent range up to a total of 255 ranges. 

Pro Tip

You don’t have to type in the cell ranges F4:F11, you may if you wish, but try selecting the range C4:C9, then adding a comma, then select F4:F11.  Alternatively, you can hold the left Ctrl key before you start selecting cells.  This is useful for adding ranges that are scattered and you want to be sure you are referencing the correct data.

4. COUNT with numbers and text.

Example of an Excel COUNT function, counting how many cells contain numbers in two ranges, some cells contain text strings.

In the example above I’ve changed a few cells, two have been changed to a written equivalent of the numbers i.e. what was “5”, is now “Five”, additionally I have accidentally slipped and hit the backslash key.  All non-numeric cells are shown in highlighted yellow.  The COUNT function will exclude these cells from the overall result.

Pro Tip                                

The COUNT function will add the number of cells containing when it sees as being a number in a range of cells.  The cell must naturally contain numbers, Excel will recognise it as a number even though its stored in text format.  They can take the shape of numbers entered directly into the cell, alternatively the COUNT function can also add up cells that contain numbers but are generated by other functions.