Unchanging array formulas

Posted by Brian on November 10, 2001 6:07 AM

I am trying to find out if it is possible to keep a
cell range in an array formula from changing.

Here is the formula:

{=SUM(IF(CON!$D$2:$D$400=1,IF(CON!$I$2:$I$400>59,1,0)))}

I set this up for someone who is a habitual row deleter
and every time a row gets deleted from the CON sheet
it changes this formula's range numbers. (This formula
is on another sheet.)

Is there any way to make the range always point from
2 to 400 whether or not rows are added or deleted?

Any help would be greatly appreciated.

Thanks
Brian

Posted by Aladin Akyurek on November 10, 2001 6:42 AM

Brian --

Change your array formula to:

{=SUM(IF(INDIRECT("CON!D2:D400")=1,IF(INDIRECT("CON!I2:I400")>59,1,0)))}

However, I'd suggest some simplification. You can equally use either the array formula

{=SUM((INDIRECT("CON!D2:D400")=1)*(INDIRECT("CON!I2:I400")>59))}

or this one that does NOT need entering as array formula:

=SUMPRODUCT((INDIRECT("CON!D2:D400")=1)*(INDIRECT("CON!I2:I400")>59))

Aladin

========

Posted by Brian on November 10, 2001 7:16 AM

Aladin
Thanks for your help. I was searching for the wrong solutions.
I tried the formula you gave me and it works great, now I can use the
MS help to figure out how it works.

Thanks again.
Brian