Formula helper gives correct answer but the cell content is differ

G

Guest

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
 
D

David Biddulph

Did you remember to enter it as an array formula (Control Shift Enter)? If
you forget that, it will give zero.
 
G

Guest

This is an array formula and must be entered with control-shift-enter
just using enter will only look at G3
 
T

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top