How do I prevent incorrect formula results appearing in cell?

M

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?
 
A

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,"?*"))
 
B

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
 

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