Excel Count Cells with Numbers (5 Simple Ways)

Excel Count Cells with Numbers (5 Simple Ways)

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.

How to Count Cells with Numbers Using COUNT Function

The syntax of that function is like this:

COUNT(value1, [value2], …) 

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.

=COUNT(First cell: Last Cell)

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.

=COUNT(C4:C9)

Enter the formula using Count function in cell D4

Step 2: Then all the counted numbers will be shown in that cell.

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.

How to Count Cells that Contain Positive Numbers

The syntax of the function is like this:

=COUNTIF(Range, Condition)

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:

=COUNTIF(B4:B10,”>0″)

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.

=COUNTIF(B4:B10,”>0″)

Enter the formula using Countif in cell C4

Step 2: After pressing Enter the total positive numbers of cells will be counted.

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:

=COUNTIF(B4:B10,”<0″)

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.

=COUNTIF(B4:B10,”<0″)

Enter the formula using Countif in cell C4 and press Enter

Step 2: See the result after applying the formula.

See the result in 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.

How to Count Cells with Numbers Based on Criteria Using COUNTIF Function

Step 1: Enter the formula in cell D4 then press Enter.

=COUNTIF(C4:C9,”>2000″)

Enter the formula using countif in cell D4 to find sales greater than 2000.

Step 2: The total number of sales greater than $2000 will be counted in cell D4.

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.

How to check numbers and count them

For this, we will use the functions like this:

=SUMPRODUCT(–ISNUMBER(First Number: Last Number))

Here I have used 2 functions in Excel. All of their explanation is given below:

=ISNUMBER(Value)

This is a logical function that gives logical output whether a value is equal to one another.

=SUMPRODUCT(array1, [array2], [array3], …)

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.

=SUMPRODUCT(–ISNUMBER(B4:B11))

Enter the below formula in Cell D4 and press Enter

Step 2: All the values will be counted then.

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.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo