How to Use COUNTIF & ISNUMBER to Count Numbers in Excel

Microsoft Excel facilitates us to estimate various types of data as well as perform monetary, arithmetic, and statistical computations. Sometimes we have a dataset where different types of data get screwed up together and we need to find out only the numbers from that mixed types of values. In these types of scenarios, we can use COUNTIF with ISNUMBER in Excel in multiple yet easiest ways. Throughout this article, I will discuss the ways in which you can use Excel COUNTIF with ISNUMBER in 4 suitable ways. So let’s jump into the main part!

How to Apply COUNTIF and ISNUMBER Function to Count Numbers in Excel: 4 Suitable Examples

In a real-world dataset containing values in different formats, we can use COUNTIF with the ISNUMBER function in Excel to count the number of numeric values. In this article we are going to use Excel 365 version, you can use any other version as well. To count values, I have used the following dataset where I have 4 columns of Rep. Name, Item, Units & Unit Cost and 13 rows. Here, in the Units column, some values are in text format. I would like to find out the total number of cells that contain numbers.

1. Combining COUNT, IF, and ISNUMBER Functions to Count Numeric Values in Excel

You can use the combination of COUNT, IF and ISNUMBER Functions to count values.
The COUNT function in Excel counts the total cells with numbers in a range. It only counts cells that have numeric values, not texts.
In Excel, there is another popular built-in function called IF that returns value based on TRUE or FALSE. The IF function allows us to compare two values based on condition. It returns TRUE if the condition is fulfilled otherwise it returns FALSE.
The third function that I am going to use is the ISNUMBER Function. The ISNUMBER function checks if the value is a number or not. It returns TRUE if the cell contains a number otherwise returns FALSE. You can use the ISNUMBER function to check if a cell contains numeric values or if the result of another function is numeric or not.
Here, I will use the Units column to count the total cells that contain numbers.

Steps:

• Select your preferred cell (i.e. B19) to have your output.

• Insert the following formula in cell B19 to count values.

Here, D5:D16 is the range of values of the Units column.

• Press ENTER Key and the result will be shown in cell B19.

Here, we got the total cell number that contains numbers.

Read More: Use ISNUMBER Function with IF and Then Statements in Excel

2. Applying COUNTIF Function with Asterisk (*) Operator to Check Numeric Values and Count Them

The COUNTIF function is the most commonly used function that can count cells based on one criterion. The COUNTIF function has two arguments- range and criteria. Range refers to the range of values and criteria in the condition. It supports logical operators and wildcards.
In this case, <> refers to not equal to any text, and Asterisk( * ) is the wildcard. To know more usage let’s go through the following part.

Steps:

• Select your preferred cell (i.e. B19) to have your output.

• Insert the following formula in cell B19.

Here, D5:D16 is the range of values of the Units column.

• Press ENTER Key and the result will be shown in cell B19.

Here, we got the total cell number that contains only numbers using COUNTIF Function with Asterisk (*) operator.

Read More: Excel ISNUMBER Not Working

3. Combine Excel SUMPRODUCT with ISNUMBER to Count Numeric Values

The SUMPRODUCT function in Excel can count cells containing numbers. It returns the sum of the products of corresponding ranges or arrays and yet is highly versatile. The default operation of SUMPRODUCT is multiplication but you can do addition, subtraction, and division as well.
In this article, I will use the SUMPRODUCT and ISNUMBER functions to count the number of cells in the Units column containing numbers. I am sure that from method 1 you have already got the idea of the ISNUMBER function. To know more let’s go through the following part.

Steps:

• Select your preferred cell to have your output. I have selected the B19 cell to show the output.

• Insert the following formula in cell B19.

 Here, D5:D16 is the range of values of the Units column.

• Press ENTER Key and the result will be shown in cell B19.

Here, we got the total cell number that contains numbers using a combination of SUMPRODUCT & ISNUMBER functions.

4. Counting Numeric Values Based on Conditions by Using Excel COUNTIF Function

COUNTIF is one of the statistical functions of Excel. From method 2, you can get a clear understanding of the purpose of using the COUNTIF Function. In this section, I will show you the easiest ways of finding values by applying the COUNTIF function that equals to, greater or less than, or not equal to a specific value.

i. Counting Numeric Values Equal to a Defined Value

Firstly, I will show you how to find the number of cells that contains data equal to a value. I will use the Unit Cost column to do this.

Steps:

• Select your preferred cell (i.e. E18) to show the output.

• Insert the following formula in cell E18.

 Here, E5:E16 is the range of values of the Unit Cost column and the criterion is 35 which is in cell E5.

• Press ENTER Key and the result will be shown in cell E18.

So, we got the total cell number that is equal to the value in E5.

ii. Counting Numeric Values Greater Than a Defined Value

Now, I am going to show you how to find the number of cells that contain data greater than a value. Again I will use the Unit Cost column.

Steps:

• Select cell E19 to show the output.

• Insert the following formula in cell E19.

 Here, E5:E16 is the range of values of the Unit Cost column and the criterion is 35.

• Press ENTER Key and you got your desired result in cell E19.

So, we got the total cell number that is greater than the value 35.

iii. Counting Values Less Than a Defined Value

Let’s move on to the next calculation. I am going to apply the COUNTIF function to find the number of cells that contain data less than a value. I will use the Unit Cost column.

Steps:

• Select cell E20 to show the output result.

• Insert the following formula in cell E20.

 Here, E5:E16 is the range of values of the Unit Cost column and the criterion is 6.

• Press ENTER Key and you got your desired result in cell E20.

So, we got the total cell number that is less than the value 6 and that is 2.

iv. Counting Total Cells with Values That Are Not Equal to a Value

Lastly, I will apply the COUNTIF function to get the number of cells that contain data not equal to a value. Here, the same Unit Cost column will be used.

Steps:

• Select cell E21 to show the output.

• Insert the following formula in cell E21.

 Here, E5:E16 is the range of values of the Unit Cost column and the criterion is 35.

• Press ENTER Key and you got your desired result in cell E21.

So, here you got the number of total cells that contain values that are not equal to 35 and the result is 10.
For your better understanding, I have attached the next picture with all of the four techniques of section 4.

Practice Section

You can use the following dataset and if you need, you can modify it too to practice by yourself. Hope it will help you to learn more about how to count values using the Excel COUNTIF function with ISNUMBER.

Download Practice Workbook

Conclusion

So the main purpose of this article is to make you clear about the usage of Excel COUNTIF with ISNUMBER function in multiple and useful ways. Hope this article will help you to perform these types of tasks effectively. If you have any suggestions or opinions please leave a comment below.

<< Go Back to Excel ISNUMBER Function | Excel Functions | Learn Excel