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.
Count Formula in Excel (Table of Contents)
Excel Count Formula
COUNT formula in excel counts all the numerical values in the supplied range. Remember it counts only numerical values nothing else. Numbers are nothing but regular numbers, dates, and time as well. So it returns the total count of numerical values in the supplied range of cells.
The syntax of COUNT Formula in Excel is given below
- [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 becomes 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)
Below are some of the examples of Count Formula in Excel.
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 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 which 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 abundant of useful tools. As we said in order to count numerical values we need to use 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 About COUNT Formula in Excel
- 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 –