Use $c$2:$c$200

The $ signs mean not to adjust the range when you copy the formula.

swjtx wrote:
>
> Hi and Thanks to both of you!
>
> Sumproduct worked. I was looking at that but the arrays were seperated
> by an * in the examples I found. I noticed you used "--".
>
> One tiny problem remains. As I drag the formula down the page, it
> correctly selects the next book# but it also increment the range
> searched on the first sheet. Any way to make it choose the same range
> without manually editing?
>
> Example:
> First Formula:
> =SUMPRODUCT(--(Sheet1!$C2:$C200=$A2),--(Sheet1!$C2:$C200>=2),--(Sheet1!$C2:$C200<3))
>
> Second Formula:
>
> =SUMPRODUCT(--(Sheet1!$C3:$C201=$A3),--(Sheet1!$C3:$C201>=2),--(Sheet1!$C3:$C201<3))
>
> I want it to choose $A3 but I want it to search the same range
> (C2:C200), not increment. Any way to do this?
>
> --
> swjtx
> ------------------------------------------------------------------------
> swjtx's Profile: http://www.excelforum.com/member.php...o&userid=29716
> View this thread: http://www.excelforum.com/showthread...hreadid=494311

--

Dave Peterson