Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

How to test a range for numbers in Excel

To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below:

Formula

=SUMPRODUCT(--ISNUMBER(range))>0

Explanation

In the example shown, the formula in xxxx is:

=SUMPRODUCT(--ISNUMBER(C5:C9))>0

How this formula works

Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if not.

Worked Example:   Understanding Cell Ranges, Auto fills, Copy/Paste, Insert Row, Column in Excel

When you supply a range of references to ISNUMBER (i.e. an array), ISNUMBER will return an array of results. In the example, this array looks like this:

{FALSE;FALSE;FALSE;TRUE;FALSE}

We want to know if this result contains any TRUE values, and the easiest way to check this is to force the TRUE FALSE values to ones and zeros, then add up the result.

Worked Example:   Cell References: Relative, Absolute and Mixed Referencing Examples

The double negative operator (–) will force the TRUE and FALSE values to 1 and 0 respectively, yielding an array like this:

Worked Example:   Count dates in given year in Excel

{0;0;0;1;0}

SUMPRODUCT then adds up the items in the array and returns the result.

Any result greater than zero means that at least one number exists in the range, so we we use “>0” to evaluate and return TRUE or FALSE.

Leave a Reply

Your email address will not be published. Required fields are marked *