DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
 

Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157DATE(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?

DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
 

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." wrote:

Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157DATE(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?

DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
 

Thanks, it worked.

"Sheeloo" wrote:

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." wrote:

Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157DATE(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?

DO NOT WANT ROW NUMBER IN FORMULA TO CHANGE
 

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"))


--
Biff
Microsoft Excel MVP


"Angie G." wrote in message
...
Thanks, it worked.

"Sheeloo" wrote:

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." wrote:

Have following formula on cell J1
(=SUMPRODUCT(--($C$3:$C$157DATE(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?