Many thanks for the swift response.

I think the root problem was that I had defined my range "Sheet2!$B3:$B10"
rather than "Sheet2!$B$3:$B$10" (note additional $ signs). However I had to
delete the name and recreate it before this change was recognised by the
formula.

I think this is what you meant by a 'dynamic range' - but interestingly,
when I had defined the range without all the $ signs, I did not get a # ref
result, I got a genuine number.

Anyway, thanks again.

"T. Valko" wrote:

There's nothing wrong with your formula.

=Sumif('Sheet2!A3:A10,"examplecriterion",Indirect( $D$7))

However, if the named range SumCells is a dynamic range then the formula
will return a #REF! error.


--
Biff
Microsoft Excel MVP


"Grahin" wrote in message
...
Hi, I wonder if anyone can help with this problem.

I am trying to write a sumif function so that the sum_range argument is a
named range which is referenced using the 'Indirect' function. However
doing
this gives the wrong answer.

Let's say that the range of cells that I want to refer to in the
'sum_range'
argument is Sheet2!B3:B10 and I have named this range "SumCells"

In Sheet 1 I write the formula

=Sumif('Sheet2!A3:A10,"examplecriterion",SumCells)

This formula works perfectly well and returns the correct result.

However, if I then enter the text 'SumCells' in sheet 1 (let's say I put
it
in cell D7), I ought to be able to use the following formula to achieve
the
same result

=Sumif('Sheet2!A3:A10,"examplecriterion",Indirect( $D$7))

However the formula now returns the wrong result.

Grateful to anyone who can shed light on why this might be.