DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE

[Angie G.]

Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157>DATE(2008,9,30)),--($C$3:$C$157<DATE(2008,11,1)),$G$3:$G$157)
If I insert a line on row 3, formula changes to $C$4 and $G$4, I want it to
stay as formula is written. Is it possible?

 

[Sheeloo]

Yes.

Try
=SUMPRODUCT(--(INDIRECT("$C$3:$C$157")>DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT("$G$3:$G$157"))

 

[Angie G.]

Thanks, it worked.

Yes.

Try
=SUMPRODUCT(--(INDIRECT("$C$3:$C$157")>DATE(2008,9,30)),--(INDIRECT("$C$3:$C$157")<DATE(2008,11,1)),INDIRECT("$G$3:$G$157"))

 

[T. Valko]

INDIRECT evaluates the references as TEXT strings and as such, will never
change when copying the formula or inserting/deleting rows/columns so you
can save a few keystrokes by eliminating the $ signs:

=SUMPRODUCT(--(INDIRECT("C3:C157")>DATE(2008,9,30)),--(INDIRECT("C3:C157")<DATE(2008,11,1)),INDIRECT("G3:G157"))