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