How to the Use the COUNTIF and the ISNUMBER Functions to Count Numbers in Excel – 4 Examples

Written by Nujat Tasnim

Last updated: Aug 4, 2024

This is an overview.

Use of COUNTIF and ISNUMBER Functions to Numeric Values

The sample dataset has 4 columns: Rep. Name, Item, Units & Unit Cost and 13 rows.

You want to find the total number of cells that contain numbers in the Units column.

Sample Excel Dataset

Example 1 – Combining the COUNT, IF, and the ISNUMBER Functions to Count Numeric Values in Excel

Steps:

Select a cell (here, B19).

Selecting a cell for output

Enter the following formula in B19.

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

D5:D16 is the range of values in the Units column.

Formula Breakdown

ISNUMBER(D5:D16)→The ISNUMBER function returns TRUE if the cell contains a number. Otherwise 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 returns 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

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

Press ENTER and the result will be displayed in B19.

Number of Numeric Values in the Excel Dataset

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

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

Steps:

Select a cell (here, B19).

Choosing a cell to get the result

Enter the following formula in B19.

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

D5:D16 is the range of values in the Units column.

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

Press ENTER and the result will be displayed in B19.

Number of Numeric Values

Read More: Excel ISNUMBER Not Working

Example 3 – Combining the Excel SUMPRODUCT with ISNUMBER to Count Numeric Values

Steps:

Select a cell (here, B19).

Choosing a cell to insert a formula

Enter the following formula in B19.

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

D5:D16 is the range in the Units column.

Formula Breakdown

ISNUMBER(D5:D16) → The ISNUMBER function checks if the value is a number. It returns TRUE if the cell contains a number. Otherwise, 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}) → The double negative (—) symbol converts 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}) →The SUMPRODUCT function counts numeric values and returns their summation.
  - Output → 8

Utilizing a Combination of SUMPRODUCT & ISNUMBER Functions to Count Numeric Values

Press ENTER and the result will be displayed in B19.

Output

Example 4 – Counting Numeric Values Based on Conditions by Using the Excel COUNTIF Function

i. Counting Numeric Values Equal to a Defined Value

Steps:

Select a cell (here, E18).

Choose a cell

Entert the following formula in E18.

=COUNTIF(E5:E16, E5)

E5:E16 is the range in the Unit Cost column and the criterion is 35 – E5.

Press ENTER to see the result in E18.

Counting Numeric Values Equal to a Defined Value

ii. Counting Numeric Values Greater Than a Defined Value

Select E19.

Choose a cell

Enter the following formula in E19.

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

E5:E16 is the range in Unit Cost column and the criterion is 35.

Press ENTER to see result in E19.

Counting Numeric Values Greater Than a Defined Value

iii. Counting Values Less Than a Defined Value

Steps:

Select E20.

Choose a cell

Enter the following formula in E20.

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

E5:E16 is the range in the Unit Cost column and the criterion is 6.

Press ENTER to see the result in E20.

Counting Values Less Than a Defined Value

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

Steps:

Select E21.

Choose a cell

Enter the following formula in E21.

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

HE5:E16 is the range in the Unit Cost column and the criterion is 35.

Press ENTER to see the result in E21.

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

The picture below showcases all methods used in Example 4.

Final Outputs

Practice Section

Practice here.

Download Practice Workbook

COUNTIF ISNUMBER Functions.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!

SaveSavedRemoved 0

Tags: Excel ISNUMBER Function

Nujat Tasnim

Nujat Tasnim, BSc in Computer Science and Engineering from American International University-Bangladesh, has a year-long tenure with ExcelDemy. Initially a Content Developer at SOFTEKO, she transitioned to the Software Team as Junior Software Quality Assurance Engineer. Currently serving as a Junior SQA Engineer at SOFTEKO, she excels in SDLC, STLC, and requirement analysis and possesses profound insights into OOP-related software development processes. Nujat is also proficient in design software like Photoshop and Illustrator, contributing diverse skills to her... Read Full Bio

We will be happy to hear your thoughts

Leave a reply Cancel reply

Submit Excel Problem