Removing cells from a range in a formula

[erock24]

I am using this formula:

=IF(SUMPRODUCT((ABS(D$17:D$70)>=8)+0),"ERROR","OK")

However, I don't want the formula to evaluate D20&D21.
When I try to use this range- .....((ABS(D17:D19,D22:D70)>=8).....
it tells me I have too many arguments?

The formula itself works perfect, I use it on another sheet. But to fit the current application I need to exclude thes 2 cells.
Any Ideas?
Thanks for your time and help.

 

[QuietRiot]

this work?

=IF(SUMPRODUCT((ABS(D$17:D$19:D$22:D$70)>=8)+0),"ERROR","OK")

 

[erock24]

It doesn't seem to be. Formula is not erroring out but is returning "error" which it shouldn't be if it wasn't taking into account d20and21. When I delete what I have in those two cells you're formula gives "ok" which means it is not excluding those 2 cells

I have large numbes in d20 and d21 whoose sum always equal zero.

 

[Aladin Akyurek]

I am using this formula:

=IF(SUMPRODUCT((ABS(D$17:D$70)>=8)+0),"ERROR","OK")

However, I don't want the formula to evaluate D20&D21.
When I try to use this range- .....((ABS(D17:D19,D22:D70)>=8).....
it tells me I have too many arguments?

The formula itself works perfect, I use it on another sheet. But to fit the current application I need to exclude thes 2 cells.
Any Ideas?
Thanks for your time and help.

Control+shift+enter:

=(MAX(IF(ISNUMBER(CHOOSE({1,2},D17:D19,D22:D70)),ABS(CHOOSE({1,2},D17:D19,D22:D70))))>=8)+0

Custom format the formula cell as:

[=0]"OK";[=1]"ERROR"

 

[erock24]

Aladin,

Thanks for the formula

This array seems to have done the trick.

{=IF((MAX(IF(ISNUMBER(CHOOSE({1,2},D17:D19,D22:D70)),ABS(CHOOSE({1,2},D17:D19,D22:D70))))>=10)+0=0,"OK","Error")}

 

[erock24]

Just for reference. is there a non-array solution?