Count Formula in Excel
Count Formula in excel is used to count the numbers of data in a range of cells, the condition to this formula is that this formula only counts the numbers and no other texts, for example, if we have a formula as =Count ( 1, e, 2) then the result displayed is 2 rather than three as the formula only counts the numbers.
- [Value 1]: This is nothing but our cell value. You can select the range of cells as a reference. If the range of cells selected in this argument itself then remaining arguments become optional.
- [Value 2]: If you select individual cells then this argument becomes active. If the range of cells selected in the first argument then this becomes optional.
How to Use COUNT Formula in Excel (with Examples)
To start off with the formula, I have a list of values from A2 to A12.
From the above list of values, I want to count how many numerical values are there.
- Step 1: Open COUNT function in Excel
- Step 2: Select Value 1 as a range of cells from A2 to A12.
- Step 3: Close the bracket and hit enter.
So, totally 7 numerical values are there in the range.
Assume you have a list of values in cells and below is the list of values from A2 to A10.
In the above, we have dates, text values, and also numbers. From this list of values, we need to count how many numerical values are there in the list. Copy this data to excel sheet.
Apply the COUNT formula in Excel and see what the count is.
COUNT formula has returned 4 as the result. But we have more than 4 numerical values as we look at the list.
All the grey marked cells are numerical values and the total of grey marked cells are 6 but the formula has returned only 4. Take a look at the A6 & A8 cells values.
Those two cell values are entered as text, so COUNT formula in Excel cannot count dates that are stored as text values.
Take look at this example now. I have few values from A2 to A10 range and below are those values.
Let’s apply Excel COUNT to count how many numerical values are there in the list.
The formula has returned 2 as the answer. But we can see more numbers here, so what is the problem.
There no problem here, because Excel COUNT formula will treat the numerical values with text values as text values only not as numerical values.
In that case, there is only one numerical value without text value i.e. A5 cell. But the formula has returned 2 as the answer instead of 1.
Now, look at the A7 cell.
It has the value of zero but formatted, that is why it is showing “-“as the result. So COUNT function treats zero also as the numerical value.
Shortcut to Count Numerical Values
Excel has an abundance of useful tools. As we said in order to count numerical values we need to use the COUNT function.
We can actually without using COUNT function we can count all the numerical values in the range just like the below image.
At the status bar of our excel, we can get the total numerical count of the selected range. For this,l you just need to enable the Numerical Count tool for the status bar.
Right-click on the Status bar.
Select Numerical Count Option here.
This will give you the numerical values count for the selected range of values.
Things to Remember
- COUNT can only count numerical values
- Status bar count can only show the count of numerical values only if the range of cells is selected.
- If you want to count all the things then you need to use COUNTA function instead of COUNT function in Excel.
This has been a guide to COUNT Formula in Excel. Here we discuss How to use COUNT Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –