Using Indirect in a Sumif Function returns the wrong answer

[Grahin]

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.

 

[Peo Sjoblom]

Works fine for me, I am assuming that you are not entering the apostrophes
in D7


--


Regards,


Peo Sjoblom

 

[T. Valko]

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.

 

[Grahin]

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.

 

[Stephen]

Just to add a little to the understanding here.

A named range defined as
=Sheet2!$B3:$B10
is not a dynamic range. Relative rather than absolute addressing does not
make a range dynamic. A dynamic range is one where the range depends on some
calculated value. For example, a range defined as
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$100),1)
is a dynamic range because its length depends on the number of populated
cells in Sheet1!A1:A100.

Defining a range with relative (or partially relative) addresses does not
invalidate the definition but can lead to quite unexpected results. Try this
out for yourself. In Sheet1, put a few simple numbers (say 1,2,3,4) in
A1:A4. Select B1. Define a named range "Range1" with the formula
=Sheet1!A1:A3
Then in B1 enter the formula
=SUM(Range1)
You will get the result of 6, as you expect. However, enter this same
formula in cell B2. The result will be 9. Surprised? Well, select cell B2
and look at the definition of "Range1" there - it will be
=Sheet1!A2:A4
Now you can see why the sum is 9: the definition of "Range1" depends on
where you are using it! That's why defining a named range other than with
absolute addresses is not to be recommended!!! But it does explain why you
got a "genuine number" for your result, rather than a #REF! error.