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