How do I prevent incorrect formula results appearing in cell?

[Marc Todd]

Using this formula:
{=SUM(IF(ISTEXT(C5:C86),1,0),IF(ISTEXT(E5:E86),1,0),IF(ISTEXT(G5:G86),1,0))},
the formula pallette displays "Formula results = 15". That is the number of
Text items I physically counted. However when I select "OK," the number "0"
is recorded in the cell instead of "15." I checked the Cell Formatting and
it was set to General. I tried "Help" but it was not there or I just didn"t
know where to look. Can you help me?

 

[Aladin Akyurek]

You need to confirm that formula with control+shift+enter instead of the
usual enter.

However, the following just needs enter and less costly qua time:

=SUM(COUNTIF(C5:C86,"?*"),COUNTIF(E5:E86,"?*"),COUNTIF(G5:G86,"?*"))

 

[Biff]

Hi!

Your formula should work if you did in fact enter it as an
array. Type the formula but use the key combo of
CTRL,SHIFT,ENTER instead of just ENTER.

Some other options:

Entered as an array:

=SUM(--ISTEXT(C5:C10),--ISTEXT(E5:E10),--ISTEXT(G5:G10))

Entered normally:

=SUMPRODUCT(--(ISTEXT(C5:C10)+ISTEXT(E5:E10)+ISTEXT
(G5:G10)))

Biff