modify formula

[RSEKAR]

Dear Sir,

I am using the following formula in a worksheet and it is working well.

=SUMPRODUCT(($G$10:$G$949>0)*($O$10:$O$949<0)*($O$10:$O$949>-10)*($S$10:$S$949))
The cell range is changed if a row is inserted or deleted within the above range. To avoid the change in range I am advised to use INDIRECT function with the above formula. I have modified the above formula with the INDIRECT function and it is not working. Kindly rewrite the above formula adding INDIRECT function in the appropriate places.
I use excel 2002
Thanking you in advance.

 

[Robert Mika]

Your data in Sheet1


=SUMPRODUCT((INDIRECT("Sheet1!$G$10:$G$949")>0)*(INDIRECT("Sheet1!$O$10:$O$949")<0)*(INDIRECT("Sheet1!$O$10:$O$949")>-10)*(INDIRECT("Sheet1!$S$10:$S$949")))

 

[mikerickson]

This non-volatile formulation is resistant to row insertion or deletion, but will change with added/removed columns.

=SUMPRODUCT(((INDEX($G:$G,10,1):INDEX($G:$G,949,1))>0)*((INDEX($O:$O,10,1):INDEX($O:$O,949,1))<0)*((INDEX($O:$O,10,1):INDEX($O:$O,949,1))>-10)*((INDEX($S:$S,10,1):INDEX($S:$S,949,1))))

 

[RSEKAR]

Dear Sir,
Both formulas are working well.The ranges are not changed when the rows deleted or inserted.
Thanking you once again.