Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Using Indirect in a Sumif Function returns the wrong answer

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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Using Indirect in a Sumif Function returns the wrong answer

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


--


Regards,


Peo Sjoblom


"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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Using Indirect in a Sumif Function returns the wrong answer

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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Using Indirect in a Sumif Function returns the wrong answer

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.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default Using Indirect in a Sumif Function returns the wrong answer

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.

"Grahin" wrote in message
...
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.






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cosine Function Returns Wrong Answer! Gordon Arnaut Excel Discussion (Misc queries) 16 April 3rd 23 02:24 PM
Row() function returns wrong row and more.. Kim Excel Worksheet Functions 5 July 11th 06 02:11 AM
My Datedif function only returns 0's in the cell what's wrong? Tom Excel Worksheet Functions 1 April 13th 06 07:43 PM
INDIRECT Function - what am I doing wrong? MACRE0 Excel Discussion (Misc queries) 2 October 5th 05 08:47 PM
Logic statement returns wrong answer. Tony Excel Worksheet Functions 2 December 2nd 04 06:07 AM


All times are GMT +1. The time now is 03:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"