Nujat Tasnim
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!
Download Practice Workbook
4 Suitable Examples of Applying COUNTIF or ISNUMBER Function 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.
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}
- 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.
- 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
- COUNT({53;56; FALSE; 59; 83; FALSE; 60; FALSE; 70; 96; FALSE; 68}) → counts numbers based on values.
- 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: [Fixed!] ISNUMBER Is Not Working in Excel
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}
- –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.
- 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
- 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.
- 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.
Read More: How to Use ISEVEN Function in Excel (2 Suitable Examples)
Similar Readings
- How to Use ISBLANK Function to Check If Cell Is Blank in Excel
- Use ISLOGICAL Function in Excel (4 Examples)
- How to Use ISTEXT Function in Excel (8 Suitable Examples)
- Use ISNA Function in Excel (3 Suitable Examples)
- How to Use ISBLANK Function in Excel (3 Examples)
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.
Read More: How to Use ISODD Function in Excel (4 Suitable Examples)
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.
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
- Use ISBLANK Function for Conditional Formatting in Excel
- How to Use ISERROR and VLOOKUP Functions in Excel
- Use IF with ISNA Function in Excel (3 Ideal Examples)
- How to Use SUMIF and ISBLANK to Sum for Blank Cells in Excel
- Use ISNA and MATCH Functions in Excel (2 Useful Examples)
- How to Use NOT and ISNA Functions in Excel (2 Examples)
- Nested IF and ISERROR Formula in Excel (2 Practical Examples)
