< Go Back

Range contains numbers

If you need to find if Range contains numbers, than this easy formula based on ISNUMBER and SUMPRODUCT can be used

FORMULA

The ISNUMBER and SUMPRODUCT functions can be used to determine a range for numbers. The general formula used to get a range for numbers is:

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

Figure 1: Range contains numbers

Figure 1 uses the formula  

=SUMPRODUCT(--ISNUMBER(C6:C1000))>0

which results in finding the range that contains numbers.

EXPLANATION

The formula works on the basis of ISNUMBER function. When the formula of the function is applied, it analyses the data sheet. The range will return true if it has number and false if does not have any number.

When there are multiple entries in the datasheet, the ISNUMBER function will give the result for each of the data in a sequence. For example, the result in figure 1 was {TRUE, TRUE, TRUE}

One of the easiest ways to check whether the values are true or not is to align the TRUE and FALSE values to 1 and 0. So based on this assumption our result was TRUE,TRUE,TRUE which will look like {1,1,1}

The SUMPRODUCT is than used to add up the items. While “>0” is used to find any number greater than 0 and give results according to that.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar