Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
I've seen this many times but now I'm really desperate. Here's the formula
- not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
Did you remember to enter it as an array formula (Control Shift Enter)? If
you forget that, it will give zero. -- David Biddulph "stebro" wrote in message ... I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
What are you trying to do? Explain. -- Don Guillett SalesAid Software "stebro" wrote in message ... I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
This is an array formula and must be entered with control-shift-enter
just using enter will only look at G3 "stebro" wrote: I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
I'm hoping there is a principle that applies
regardless of the funtion There is. That principle is arrays. The formula you posted is an array formula. An array formula needs to be entered with a combination of keys. Those keys are CTRL,SHIFT,ENTER. That is, hold down both the CTRL and SHIFT keys then hit ENTER. If done properly Excel will enclose the formula in squiggly brackets: { } {=SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0)))} The squiggly brackets denote an array. In this case the entire formula is the array. You can't just type these brackets in. You *must* use the key combo. Also, anytime you edit an array formula it must be re-entered as an array using the key combo. Here's another way to write that formula that contains an array *but is not an array formula*: =SUM(COUNTIF(G3:G22,{"DUP","NE"})) Note that the array is within the formula. This type of array is called an array constant, an array of constant values. Yet another way to write that formula: =COUNTIF(G3:G22,"DUP")+COUNTIF(G3:G22,"NE") Biff "stebro" wrote in message ... I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is di
Thanks all - this is it.
-sb "bj" wrote: This is an array formula and must be entered with control-shift-enter just using enter will only look at G3 "stebro" wrote: I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
If you forget that, it will give zero.
Depends on where the formula is physically located. http://img86.imageshack.us/img86/6425/arrayjr3.jpg Biff "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Did you remember to enter it as an array formula (Control Shift Enter)? If you forget that, it will give zero. -- David Biddulph "stebro" wrote in message ... I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula helper gives correct answer but the cell content is differ
=SUM(COUNTIF(G3:G22,{"DUP","NE"}))
"stebro" wrote: I've seen this many times but now I'm really desperate. Here's the formula - not that it matters I think - I'm hoping there is a principle that applies regardless of the funtion. =SUM(IF(G3:G22="DUP",1,IF(G3:G22="NE",1,0))) In the helper window I see the formula correctly identifies one condition where the range contains "DUP" and 3 cases where it finds "NE" and shows a result of 4. However the value that displays as a result of this formula is zero. How can I get the right answer to appear in the cell ???? Thanks, sb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add up cell content to get answer at bottom of sheet | Excel Discussion (Misc queries) | |||
Fill down produces correct formula but wrong answer | Excel Worksheet Functions | |||
Formula correct, answer wrong | Excel Discussion (Misc queries) | |||
Cond Format & helper-cell based "duplicate rec" tricked by content | Excel Discussion (Misc queries) | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |