sumproduct

[nowfal]

I have the following sumproduct formula it is calculating one colomn . But the problem coming when somebody trying to delete one row inbetween then inside the formula cell number is decreasing
=SUMPRODUCT((COURIER!B4:B996=TODAY())*(COURIER!D4:D996="S")*(COURIER!C4:C996))
like this. It was B1000 when i made first. any solution for not to reduce the number.
Secondly i wanted to change the 996 to the end of excel limit (65536)any short cut to write this.

thanks alot
with regards
nowfal

[Bob Phillips]

You could use INDIRECT

=SUMPRODUCT(--(INDIRECT("COURIER!B4:B1000")=TODAY()),--(INDIRECT("COURIER!D4
:D1000")="S"),INDIRECT("COURIER!C4:C1000"))

--
HTH

Bob Phillips

"nowfal" <[email protected]> wrote in
message news:[email protected]...
>
> I have the following sumproduct formula it is calculating one colomn .
> But the problem coming when somebody trying to delete one row inbetween
> then inside the formula cell number is decreasing
>
=SUMPRODUCT((COURIER!B4:B996=TODAY())*(COURIER!D4:D996="S")*(COURIER!C4:C996
))
> like this. It was B1000 when i made first. any solution for not to
> reduce the number.
> Secondly i wanted to change the 996 to the end of excel limit
> (65536)any short cut to write this.
>
> thanks alot
> with regards
> nowfal
>
>
> --
> nowfal
> ------------------------------------------------------------------------
> nowfal's Profile:
http://www.excelforum.com/member.php...o&userid=10003
> View this thread: http://www.excelforum.com/showthread...hreadid=388286
>

[nowfal]

Thanks Bob, tested, this is the exact formula i wanted.
by
nowfal