Hi Sam,

When you put a $ in front of the reference you don't want to change then
that will solve it for you.
In this case, do...
SUMPRODUCT(--('Sheet 1!$C$1:$C$10=A7),--('Sheet 1!$D$1:$D$10=B7))

Typing Reference in Excel Help says:
Relative vs. absolute references Depending on the task you want to perform
in Excel, you can use either relative cell references, which are references
to cells relative to the position of the formula, or absolute references,
which are cell references that always refer to cells in a specific location.
If a dollar sign precedes the letter and/or number, such as $A$1, the column
and/or row reference is absolute. Relative references automatically adjust
when you copy them, and absolute references don't.

Rob

"SamGB" wrote in
message ...

Here is the formula i have:

SUMPRODUCT(--('Sheet 1!C1:C10=A7),--('Sheet 1!D1:D10=B7))

I need to paste this formula into 20+ Decending cells...When i copy the
formula down the Cell reference changes i.e A7 goes to A8 etc. which is
my desired result. However the range also increase C1:C10 goes to
C2:C11 which i dont want as it gives false results.

How can you stop the range changing??
Many Thanks


--
SamGB
------------------------------------------------------------------------
SamGB's Profile:
http://www.excelforum.com/member.php...o&userid=31263
View this thread: http://www.excelforum.com/showthread...hreadid=516398