Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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!

overview image of excel countif or isnumber function to count numbers


Download Practice Workbook


4 Suitable Examples of Applying COUNTIF or ISNUMBER Functions to Count Numbers in Excel

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.

dataset for using Excel COUNTIF with ISNUMBER function


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

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.

selecting preferred cell to get output after applying COUNT,IF and ISNUMBER functions

  • 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

inserting formula to count total cells number using Excel COUNT, IF and ISNUMBER function

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

Total cells number using COUNT, IF and ISNUMBER Functions

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.

selecting preferred cell to apply Excel COUNTIF function with Asterisk(*)

  • Insert the following formula in cell B19.
=COUNTIF(D5:D16, "<>*")

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

inserting formula to count total cells number using Excel COUNTIF function with Asterisk(*)

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

Total cells number after applying Excel COUNTIF function with Asterisk(*)

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

Read More: [Fixed!] ISNUMBER Is Not Working in Excel


3. Utilizing a Combination of SUMPRODUCT & ISNUMBER Functions 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.

selecting 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

inserting formula to count total cells number using a combination of Excel SUMPRODUCT & ISNUMBER Functions

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

Total cells number after applying combination of Excel SUMPRODUCT & ISNUMBER Functions

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

Read More: How to Use ISEVEN Function in Excel (2 Suitable Examples)


Similar Readings


4. Counting Numeric Values Based On Conditions by Using 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.


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

Selecting cell to show the result

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

Applying Excel COUNTIF function to get the number of total cells equal to value

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

Total cells number after applying Excel COUNTIF with ISNUMBER function

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


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

Selecting cell to show the result

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

Applying Excel COUNTIF with ISNUMBER function to get the number of total cells that is greater than a value

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

Total cells number after applying Excel COUNTIF with ISNUMBER function that is greater than a value

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


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

overview image of excel countif with isnumber function

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

Applying Excel COUNTIF with ISNUMBER function to get the number of total cells that is less than a value

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

Total cells number after applying Excel COUNTIF with ISNUMBER function that is less than a value

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

Read More: How to Use ISODD Function in Excel (4 Suitable Examples)


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

Selecting cell to show the result

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

Applying Excel COUNTIF with ISNUMBER function to get the number of total cells that is not equal to a value

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

Total cells number after applying COUNTIF function that is not equal to a value

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.

calculating number of numeric values based on conditions using Excel COUNTIF with ISNUMBER function


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.

dataset for practicing counting using Excel COUNTIF with ISNUMBER function


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.


Related Articles

Nujat Tasnim

Nujat Tasnim

Hello everyone!! Welcome to my profile. I'm currently working and researching Microsoft Excel, and I'll be sharing articles about it here. My most recent academic qualification was BSc in Computer Science and Engineering from American International University-Bangladesh.I have a bachelor's degree in computer science and am really interested in research and development. I'm always enthusiastic about picking up new knowledge and abilities. I enjoy cooking and experimenting with new recipes in my free time.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo