How to Count If Cell Contains Number (Easiest 7 Ways)

how to count if cell contains number

If you are looking for some of the easiest ways to count if a cell contains number in Excel then you will find this article worthy of it. Sometimes different texts and numbers and other types of variables get mixed up in a column. If you want to count cells based on numbers then let’s go through this article.

Download Workbook

7 Ways to Count If Cell Contains Number

I have used the following table wherein the Size column I have both text and number variables. Here, I would like to count the number of cells here based on the numbers in this column. You will get to know the ways by using the following example.

dataset

Method-1: Using COUNT Function to Count Cells with Number

You will be able to count cells containing numbers by using the COUNT function. In this case, I will use the Size column to count the number of cells containing numbers. Here, I have added the Count column for this purpose.

using COUNT function

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=COUNT(C5:C13)

Here, C5:C13 is the range of values
The COUNT function will count the number of cells containing numbers.

using COUNT function

Step-02:
➤Press ENTER
Afterward, you will get the number of cells containing numbers in the Size column.

how to count if cell contains number

Method-2: Using COUNTIF Function to Count Cells with Number

You will be able to count cells containing numbers by using the COUNTIF function. Here, I will use the Size column to count the number of cells containing numbers.

using COUNTIF function

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=COUNTIF(C5:C13,"<>*")

Here, C5:C13 is the range of values
and before the wildcard, <> is used which means Not Equal to any texts.

using COUNTIF function

Step-02:
➤Press ENTER
After that, you will get the number of cells containing numbers in the Size column.

using COUNTIF function

Method-3: Counting Cells with Number and Text

Suppose, now you want to count the total number of cells containing both numbers and texts in the Size column. To do this you have to use the COUNTA function.

using COUNTA function

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=COUNTA(C5:C13)

Here, C5:C13 is the range of values
The COUNTA function will count the number of cells containing numbers and texts.

using COUNTA function

Step-02:
➤Press ENTER
In this way, you will get the number of cells containing numbers and texts in the Size column.

using COUNTA function

Method-4: Counting Cells with Numbers in a Filtered Table

Suppose you want to count numbers in a filtered data table but in this case, you will get the total numbers not only the shown values in the filtered column. But you can count the visible values only after filtering by following this method.
You have to use the SUBTOTAL function before filtering the data table.

count numbers in a filtered table

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=SUBTOTAL(102,C5:C13)

Here, 102 is used for using the COUNT function
C5:C13 is the range of values

count numbers in a filtered table

Step-02:
➤Press ENTER
Then you will get the number of cells containing numbers before filtering in the Size column.

count numbers in a filtered table

Step-03:
➤Filter the data table according to your needs
Then you will get the number of cells containing numbers in the Size column which is not hidden.

count numbers in a filtered table

Method-5: Counting Cells with Single Criteria

Assume now you want to count the number of cells in the Price column containing numbers.
Here, you will count the cells based on criteria that the Price should be only for Shoe as a Product.

count cells with number with single criteria

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=COUNTIF(B5:B13,"*Shoe*")

Here, B5:B13 is the range of values
The shoe is the criteria, between the Wildcards the criteria name has been written for matching partially of the text Shoe

count cells with number with single criteria

Step-02:
➤Press ENTER
In this way, you will get the number of cells containing numbers based on criteria in the Price column.

count cells with number with single criteria

 

Method-6: Using COUNTIFS Function to Count Cells with Multiple Criteria

Suppose, now you want to count cells containing numbers in the Price column based on two criteria. The first criteria here is the same as in Method-5 and the second criteria is Price should be greater than $1,500.00. You can do this by using the COUNTIFS function.

count cells with number with multiple criteria

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=COUNTIFS(B5:B13,"*Shoe*",D5:D13,">1500")

Here, B5:B13 is the first criteria range
Shoe is the first criteria
D5:D13 is the second criteria range
“>1500” is the second criteria.

count cells with number with multiple criteria

Step-02:
➤Press ENTER
Afterward, you will get the number of cells containing numbers based on multiple criteria in the Price column.

count cells with number with multiple criteria

Method-7: Using SUMPRODUCT Function to Count Cells with Number

You will be able to count cells containing numbers by using the SUMPRODUCT function. In this case, I will use the Size column to count the number of cells containing numbers.

using SUMPRODUCT function

Step-01:
➤Select the output cell in the Count column
➤Type the following formula

=SUMPRODUCT((--ISNUMBER(C5:C13)))

Here, C5:C13 is the range,
The ISNUMBER function will check out if there are numbers and then will return TRUE and if there is no number then it will return FALSE. Then will convert TRUE into 1 and FALSE into 0.
Then the SUMPRODUCT function will sum up the values.

using SUMPRODUCT function

Step-02:
➤Press ENTER
Then you will get the number of cells containing numbers in the Size column.

how to count if cell contains number

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

Practice

Conclusion

In this article, I tried to cover the easiest ways to count if a cell contains a number in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.

 

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo