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.