Purpose

Return value

Syntax

=ISNA(value)
  • value - The value to check if #N/A.

Using the ISNA function

The ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. The ISNA function takes one argument, value , which is typically a cell reference.

Examples

If A1 contains the #N/A error, ISNA returns TRUE:

=ISNA(A1) // returns TRUE

ISNA returns FALSE for other values and errors:

=ISNA(100) // returns FALSE
=ISNA(5/0) // returns FALSE

You can use the ISNA function with the IF function test for #N/A and display a friendly message if the error occurs. For example, to display a message if A1 contains #N/A and the value of A1 if not:

=IF(ISNA(A1),"message",A1)

The IFNA function is a more efficient way to trap the #N/A error. See VLOOKUP without NA error for an example.

Return #N/A

To explicitly return the #N/A error in a formula, you can use the NA function :

=NA() // returns #N/A error

The following will return true:

=ISNA(NA()) // returns TRUE

Count #N/A errors

To count cells in a range that contain #N/A errors, you can use the SUMPRODUCT function like this:

=SUMPRODUCT(--ISNA(range))

The double negative coerces the TRUE and FALSE results from ISNA into 1s and 0s and SUMPRODUCT sums the result.

Notes

  • The IFNA function is a more efficient way to trap and handle the #N/A error.

Purpose

Return value

Syntax

=ISNONTEXT(value)
  • value - The value to check.

Using the ISNONTEXT function

The ISNONTEXT function returns TRUE when a cell contains any value except text. This includes numbers, dates, times, errors, and formulas that return non-text results. ISNONTEXT also returns TRUE when a cell is empty.

The ISNONTEXT function takes one argument , value , which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is not text, the ISNONTEXT function will return TRUE. If value is text, ISNONTEXT will return FALSE.

Examples

The ISNONTEXT function returns TRUE for numbers and FALSE for text:

=ISNONTEXT(100) // returns TRUE
=ISNONTEXT("apple") // returns FALSE

If cell A1 contains the number 100, ISNONTEXT returns TRUE:

=ISNONTEXT(A1) // returns TRUE

If cell A1 is empty, ISNONTEXT returns TRUE:

=ISNONTEXT(A1) // returns TRUE

If a cell contains a formula, ISNONTEXT checks the result of the formula:

=ISNONTEXT(2+2) // returns TRUE
=ISNONTEXT(10 &" apples") // returns FALSE
=ISNONTEXT(A1&B1) // returns FALSE

Note: the ampersand (&) is the concatenation operator in Excel. When values are concatenated, the result is text.

Count text non values

To count cells in a range that do not contain text with the ISNONTEXT function, you can use the SUMPRODUCT function like this:

=SUMPRODUCT(--ISNONTEXT(range))

The double negative coerces the TRUE and FALSE results from ISNONTEXT into 1s and 0s and SUMPRODUCT sums the result. You can also use the COUNTIF function to count cells that do not contain text, as explained here .

Notes

  • When value is a number, ISNONTEXT returns TRUE.
  • When value is any error, ISNONTEXT returns TRUE.
  • When value is an empty cell, ISNONTEXT returns TRUE.