Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 26th 05, 07:17 AM
Marc Todd
 
Posts: n/a
Default How do I prevent incorrect formula results appearing in cell?

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?

  #2   Report Post  
Old January 26th 05, 07:39 AM
Aladin Akyurek
 
Posts: n/a
Default

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,"?*"),COU NTIF(G5:G86,"?*"))

Marc Todd wrote:
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?

  #3   Report Post  
Old January 26th 05, 07:57 AM
Biff
 
Posts: n/a
Default

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

-----Original Message-----
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?
.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell shows formula and not the result of the formula. stumpy1220 Excel Worksheet Functions 2 January 14th 05 05:11 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 07:18 AM
I want the results of a formula to show in cell, NOT THE FORMULA! ocbecky Excel Discussion (Misc queries) 4 December 10th 04 08:39 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM
can i colour a cell on basis of results of a formula e.g clour bl. K Excel Worksheet Functions 3 November 4th 04 06:18 PM


All times are GMT +1. The time now is 04:33 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017