Md. Abdullah Al Murad
Excel provides different functions where we can count cells based on different conditions and criteria. This article will show you those functions where we only count cells that contain numbers and their proper usage.
Downloadable Practice WorkBook
5 Ways to Count Cells with Numbers in Excel
1. How to Count Cells with Numbers Using COUNT Function
Let’s assume a dataset of some salespersons with their monthly total sales. There may be some blank cells where the salesperson has no sales on that day. Now we will count the number of sales in November using the COUNT function.
The syntax of that function is like this:
Here in the parameter of the COUNT function, we could pass the range of the cells that we need to calculate.
For using the formula function’s parameters like this in our workbook.
In the first section, we will select the first cell number or from where we want to start counting and the other section will contain the last cell number or where we want to stop the counting.
Step 1: Enter the below formula in cell D4 and press Enter.
Step 2: Then all the counted numbers will be shown in that cell.
2. How to Count Cells that Contain Positive Numbers
Sometimes we may need to count positive numbers cells in Excel. In that case, we may use the COUNTIF function, and using the condition of the positive number we can easily find out the positive number from any dataset. Let’s assume we have a dataset of some numbers. We may have both positive and negative numbers. Now we will figure out the cells with positive numbers by using the COUNTIF function.
The syntax of the function is like this:
So the first part of the parameter we need to select the range of our cells and the second part of the parameter will carry the criteria or condition. In our workbook the formula will be like this:
Formula Explanation
As we are selecting from cell B4 to B10 cells that’s why the range will be B4:B10 and for condition, I have used “>0” because we need to count all positive numbers.
Step 1: Enter the below formula in cell C4 and press Enter.
Step 2: After pressing Enter the total positive numbers of cells will be counted.
3. How to Count Cells that Contain Negative Numbers
For counting negative number cells, we will consider the same scenario mentioned in method 2. Instead of counting positive number cells here, we will find out the negative number cells. For this the formula will be like this:
As we are going to count the negative numbers which should be less than 0 that’s why I have used the “<0” condition in the second of the function parameters.
Step 1: Enter the underneath formula in cell C4 and press Enter.
Step 2: See the result after applying the formula.
4. How to Count Cells with Numbers Based on Criteria Using COUNTIF Function
Now let’s see the way of counting cells that contain numbers based on condition. For this method, we will consider the same example which was used in method 1. Here I will show the total sales number where the sales value is greater than $2000 using the COUNTIF function.
Step 1: Enter the formula in cell D4 then press Enter.
Step 2: The total number of sales greater than $2000 will be counted in cell D4.
5. How to Check Numbers and Count the Numbers
In this method, I will show firstly how to check for numbers and then count the cells which are containing numbers. For this let’s think we have a list of some Names and Numbers in a column. Using two functions SUMPRODUCT and ISNUMBER we will check the numbers and count them.
For this, we will use the functions like this:
Here I have used 2 functions in Excel. All of their explanation is given below:
This is a logical function that gives logical output whether a value is equal to one another.
With this function, we can easily do multiplication, addition, subtraction, and division. Simply we can add ranges or arrays using this function by passing the ranges or arrays into the parameter.
Step 1: Enter the below formula in Cell D4 and press Enter.
Step 2: All the values will be counted then.
Conclusion
These are the ways of counting cells with numbers in Excel. I have shown all the methods with their respective examples. If you have any other method of achieving this then please feel free to share it with us.
