Formula range #Value! error

[Grammarjunkie]

Normally when I write formulas, I have no need for ranges. But in this case, I need L10:L75.

=IF(L10:L75="","blahblah","blahblahblah")

Doing it individually doesn't return a #value! error, but doing the range does. As someone who has never needed a range before, I'm not sure if I'm doing something wrong or if I'm missing something entirely.

Would anyone mind helping me fix the value error. I don't want to do an =IF(AND for every single cell L10 through L75 haha.

Thanks in advance.

 

[Caleeco]

Try this instead, does it do what you require?

=IF(COUNTA(L10:L75)>0,"blahblah","blahblahblah")

EDIT: I modified the code to use a countA instead ^^ please try above formula

Regards
Caleeco

 

[63falcondude]

You can try =IF(COUNTBLANK(L10:L75)>0,"blahblah","blahblahblah")

Haven't tested it but it should work.

 

[Grammarjunkie]

Try this instead, does it do what you require?

=IF(COUNTA(L10:L75)>0,"blahblah","blahblahblah")

EDIT: I modified the code to use a countA instead ^^ please try above formula

Regards
Caleeco

Nope. It does function for half of what I needed, but not the other half.
Like if one of those fields has anything in it, it reads blahblah like it's supposed to. But even if nothing is in it, it reads blahblah.
And if I flip the alligator, it does the reverse. But again, not both.

 

[Caleeco]

Nope. It does function for half of what I needed, but not the other half.
Like if one of those fields has anything in it, it reads blahblah like it's supposed to. But even if nothing is in it, it reads blahblah.
And if I flip the alligator, it does the reverse. But again, not both.

Works fine for me, just tested it again now. Do you have formulas in cells L10:L75 displaying blanks by any chance?

Regards
Caleeco

 

[Grammarjunkie]

You can try =IF(COUNTBLANK(L10:L75)>0,"blahblah","blahblahblah")

Haven't tested it but it should work.

It doesn't either.
It does do one half, but it doesn't do the if the reverse is true. It's not completing both functions.

For clarity, these fields might have information populated into them. So I want it to be =IF(L10:L75="","No alerts","ALERTS!")

So essentially if any of those fields has information in it, there will be an alert at the top. With your formula, it reads "No alerts" no matter what.

 

[Grammarjunkie]

Works fine for me, just tested it again now. Do you have formulas in cells L10:L75 displaying blanks by any chance?

Regards
Caleeco

Very much so. Each cell has its own formula going on. And they do result in blanks depending on other stuff on the spreadsheet.

 

[Jonmo1]

So a formula returning "" is NOT blank, it's actually a TEXT string.
So COUNTA does indeed count it as a Non Blank cell.

So to clarify what you want.
If ANY 1 or more cell in the range is NOT blank, you want to return "xxx"
If ALL Cells in the range are blank, you want to return "zzz"

Fortunately, COUNTBLANK actually consideres formula blanks as blank, so we can use that instead and reverse the logic.
=IF(COUNTBLANK(L10:L75)=66,"All Cells Are Blank","There is at least 1 Non Blank Cell")

And to avoid having to count the rows to come up with the 66
=IF(COUNTBLANK(L10:L75)=ROWS(L10:L75),"All Cells Are Blank","There is at least 1 Non Blank Cell")

 

[Grammarjunkie]

So a formula returning "" is NOT blank, it's actually a TEXT string.
So COUNTA does indeed count it as a Non Blank cell.

So to clarify what you want.
If ANY 1 or more cell in the range is NOT blank, you want to return "xxx"
If ALL Cells in the range are blank, you want to return "zzz"

Fortunately, COUNTBLANK actually consideres formula blanks as blank, so we can use that instead and reverse the logic.
=IF(COUNTBLANK(L10:L75)=66,"All Cells Are Blank","There is at least 1 Non Blank Cell")

And to avoid having to count the rows to come up with the 66
=IF(COUNTBLANK(L10:L75)=ROWS(L10:L75),"All Cells Are Blank","There is at least 1 Non Blank Cell")

That's it!

Thank you for taking the time to explain why it wasn't doing what I wanted. I've done something similar in the past with far fewer cells, and I never ran into this issue. But there clearly must have been some variable I don't remember that allowed it to consider that it was blank. Either way, it's working now, and I can definitely use this technicality in the future.

Thank you so much for your help.

 

[Jonmo1]

You're welcome.

Here's another way

=IF(SUM(COUNTIF(L10:L75,{"?*","<=9.99999999999999E+307"})),"at least 1 non blank","All are blank")