Google Sheets – Using IF Statement with Ranges

google sheets

How do I make it so that if any number in the range C6:D11 on PAGE is less than zero, output "Some words" and if not, output "Some other words"?

I tried using =IF(PAGE!C6:D11 < 0, "Some words", "Some other words"), but I that gave me an error saying "Range has no entry corresponding to this cell".

Best Answer

The problem is that you are passing the IF function an array (range), while it only supports a single value.

Try

=IF(MIN(PAGE!C6:D11) < 0, "Some words", "Some other words")

The MIN function finds the minimum value in your range.

Related Solutions

Google Sheets – Sum of Two Numbers with a Minimum of Zero

You can use the MAX function for this, since

MAX( A3+B3, 0 )

will return 0, when the sum is negative.

Google Sheets – IF Statement in Google Spreadsheet

What you need is:

=ARRAYFORMULA( SUM( IF( ISERROR( SEARCH("subtotal", C6:C65)), IF(E6:E65 >=0, E6:E65, 0 ), 0)))

To break down the logic:

ARRAYFORMULA (SUM(

is the equivalent of doing:

= IF( ISERROR( SEARCH("subtotal", C6)), IF(E6 >=0, E6, 0 ), 0)

for each line, then summing the total. Note that you use a range reference (eg C6:C65) instead of a single cell reference (eg C6) when using ARRAYFORMULA.

Next it checks if subtotal is not in the (semi-) adjacent cell (otherwise it returns 0):

IF( ISERROR( SEARCH("subtotal", C6))

If "subtotal" is not in the adjacent cell, next check if the value is positive

IF(E6 >=0

If value is positive return the value, otherwise return 0.

Best Answer

Related Solutions

Google Sheets – Sum of Two Numbers with a Minimum of Zero

Google Sheets – IF Statement in Google Spreadsheet

To break down the logic:

Related Topic