How to the Use COUNTIF & ISNUMBER to Count Numbers in Excel

This is an overview.

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

Use COUNTIF with the ISNUMBER function in Excel to count 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.

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

The COUNT function counts the total cells with numeric values in a range. The IF function compares two values based on condition. It returns TRUE if the condition is fulfilled. Otherwise, it returns FALSE. The ISNUMBER function checks if the value is a number.

Steps:

Select a cell (here, B19).

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

Press ENTER and the result will be displayed in B19.

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

The COUNTIF function has two arguments- range and criteria. Range refers to the range of values and criteria is the condition. It supports logical operators and wildcards. <> refers to not equal to, and Asterisk( * ) is the wildcard.

Steps:

Select a cell (here, B19).

Enter the following formula in B19.

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

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

Press ENTER and the result will be displayed in B19.

Read More: Excel ISNUMBER Not Working

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

The SUMPRODUCT function counts cells containing numbers. It returns the sum in ranges or arrays.

Steps:

Select a cell (here, B19).

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