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?