# Removing cells from a range in a formula

#### erock24

##### Well-known Member
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.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### QuietRiot

##### Well-known Member
this work?

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

#### erock24

##### Well-known Member
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.

##### MrExcel MVP
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

##### Well-known Member

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

##### Well-known Member
Just for reference. is there a non-array solution?

Replies
3
Views
270
Replies
6
Views
375
Replies
3
Views
562
Replies
5
Views
184
Replies
4
Views
263

1,191,709
Messages
5,988,236
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back