It’s very easy to count a number of cells that contain numbers for small pieces of data, but if your boss or interviewer asks you to do such a task on large datasets in excel. You might think twice to do such task manually, but don’t panic here ms excel kept you an surprise.
If you want to count a number of cells or want to check how many cells are containing numbers vertically or horizontally, then this function will help you in those situations. Basically, this function is used in excel to find the cell count, which contain only numbers.
Let us understand practically:-
Syntax of Count Function:-
=Count(value1,value2, value3….,)
The syntax of the Count function is very simple to understand actually you have to select the cells which only contain numbers. Select cell by cell or a range of cells, but I personally recommend using “Range of cells” to use. For example “A2:A10” or “A2:A100”, because it can save more time on data work.
Here, we took some raw data which has alpha, alpha-numeric, and numeric values in columns A, B, & C respectively.
Note – In “Column B” values are a combination of numeric & alpha-numeric values. Alpha-numeric is those values that include Text, Numbers, & Special characters.
Suggestion – Please consider the first row as Header values.
Now, Let’s set different conditions by using the same function
- Count with Alpha
- Count with Alpha/Numeric
- Count with Numeric
let us check how many cells contain only numbers based on the criteria wise by using this function.
- Criteria 1 – Apply the function to “Column A” which is the customer name that has alpha values.
Steps to apply the formula:-
Step 1 – Choose the cell where the function should be indicated, right now the active cell is “G4”
Step 2 – Use the equal sign “=” from the keyboard on the cell as showing below then try to type function COUNT & press TAB button to open the bracket.
Step 3 – Select the range “A2:A11” from “Column A” as shown below & press ENTER key for end results.
The end returned value is Zero as shown in the below image, It means there are no numeric values in “Column A”. The Count function only evaluates if the range or cell contains numbers.
- Criteria 2 – Apply the count function to “Column B” which is the IP Address of the customer. The selected range is a combination of alpha-numeric & numeric values.
Steps to apply the formula:-
Step 1 – Choose the cell where the function should be indicated, right now the active cell is “H4”
Step 2 – Use the equal sign “=” from the keyboard on the active cell (H4) then type COUNT & to open the bracket press TAB button on the keyboard.
Step 3 – Select the range “B2:B11” from “Column B” as shown below & press ENTER key for end results.
Step 4 – The return value is showing 4. It means there are four cells which have number values.
Here we can see from the below image that “Column B” has 4 cells that have number values. So, it gives the returned value of 4, This function makes it easier to find out the cell count which has number values.
- Criteria 3 – Again we apply the count function to “Column C” which only contains only numbers.
Steps to apply the formula:-
Step 1 – Choose the cell where the function should be indicated, right now the active cell is “I4”
Step 2 – Use the equal sign “=” from the keyboard on the active cell (I4) then type COUNT & to open the bracket press TAB button on the keyboard.
Step 3 – Select the range “C2:C11” from “Column C” as shown below & press ENTER key for end results.
Step 4 – The return value is showing 10. It means all values from “C2:C11” contains numbers.
The output of the function :-
- Count with Alpha – Column “A” does not have any numeric value. So, it returned as “Zero 0”
- Count with Alpha/Numeric – Column “B” has only 4 cells that contain numbers.
- Count with Numeric – Column “C” has an entire range of numbers. So, it returned as 10.
Conclusion:- This function can be used in those conditions, where you want to find cell count.
Please practice in the hope that you will become an expert.