Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cosine Function Returns Wrong Answer! | Excel Discussion (Misc queries) | |||
Row() function returns wrong row and more.. | Excel Worksheet Functions | |||
My Datedif function only returns 0's in the cell what's wrong? | Excel Worksheet Functions | |||
INDIRECT Function - what am I doing wrong? | Excel Discussion (Misc queries) | |||
Logic statement returns wrong answer. | Excel Worksheet Functions |