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

{=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))