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

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

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

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

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

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

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

Formula helper gives correct answer but the cell content is differ
 

or NON array entered
=SUMPRODUCT((G3:G22={"dup","ne"})*1)

--
Don Guillett
SalesAid Software

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

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