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!

**Table of Contents**Expand

## 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.

`=COUNT(IF(ISNUMBER(D5:D16),D5:D16))`

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

**Formula Breakdown**

**ISNUMBER(D5:D16)â†’The ISNUMBER function**returns**TRUE**if the cell contains a number otherwise returns**FALSE**.**Output â†’***{TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}*

**IF(ISNUMBER(D5:D16),D5:D16) â†’**becomes**IF({TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}****, D5:D16) â†’The IF function**will give us the numeric value for**TRUE**.**Output â†’ {53;56; FALSE; 59; 83; FALSE; 60; FALSE; 70; 96; FALSE; 68}**

**COUNT(IF(ISNUMBER(D5:D16),D5:D16)) â†’**becomes**COUNT({53;56; FALSE; 59; 83; FALSE; 60; FALSE; 70; 96; FALSE; 68}) â†’**counts numbers based on**values**.**Output â†’ 8**

- 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**.

`=COUNTIF(D5:D16, "<>*")`

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**.

`=SUMPRODUCT((--ISNUMBER(D5:D16)))`

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

**Formula Breakdown**

**ISNUMBER(D5:D16) â†’ The ISNUMBER function**checks if the value is a**number**or not. It returns**TRUE**if the cell contains a number otherwise returns**FALSE**.**Output â†’***{TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}*

**â€“ISNUMBER(D5:D16) â†’**becomes**â€“ISNUMBER({TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}) â†’**Here, the double**negative**(**â€”**) symbol will convert the boolean values into numeric values.**Output â†’***{1;1;0;1;1;0;1;0;1;1;0;1}*

**SUMPRODUCT((â€“ISNUMBER(D5:D16))) â†’**becomes**SUMPRODUCT(***{1;1;0;1;1;0;1;0;1;1;0;1}***) â†’**Here,**the SUMPRODUCT function**counts the numeric values and returns the summation of those numeric values.**Output â†’ 8**

- 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**.

`=COUNTIF(E5:E16, E5)`

**Â **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**.

`=COUNTIF(E5:E16,">35")`

**Â **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**.

`=COUNTIF(E5:E16,"<6")`

**Â **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**.

`=COUNTIF(E5:E16,"<>35")`

**Â **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**