OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Countng text

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Mon Dec 01, 2008 3:18 pm    Post subject: Countng text Reply with quote

Now for another question. How do I count cells with text?

That is, I have a whole array filled with text and no text, detrmined by a formula. All the counts I've tried either count the cells as a whole or none of the cells. I just want it to count the ones that actually have text showing.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Dec 01, 2008 5:25 pm    Post subject: Reply with quote

With an array as seen in the formula, use this:

=SUMPRODUCT(ISTEXT(A1:C14))

It must be entered using CTRL-SHIFT-ENTER, not just the Enter key.

David.
Back to top
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Mon Dec 01, 2008 6:24 pm    Post subject: Reply with quote

Does not work. It is still counting the cells with a formula.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Dec 01, 2008 7:15 pm    Post subject: Reply with quote

Xelebes3 wrote:
Does not work. It is still counting the cells with a formula.


You didn't mention "formula". I'll rethink, but am sure Villeroy or someone will come up with something.

EDIT: I just checked, and inserted the formula =SUMPRODUCT(ISTEXT(A1:C14)) with just the Enter key, and it seems to work for me. The question remains "What formula, for example?" since mine was a simple SUM() which gives a numerical result. The summation cell was included in the range indicated in the above formula. I'm just learning something about the SUMPRODUCT() function myself and frankly guessed it's use in this instance.

David.
Back to top
View user's profile Send private message
Xelebes3
Power User
Power User


Joined: 30 Nov 2008
Posts: 50
Location: Canada

PostPosted: Mon Dec 01, 2008 7:47 pm    Post subject: Reply with quote

The formula turns out a numerical response.

A formula like this:

=IF(LEN($B2)=3;$B2;"")

Where the cell B2 has text.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue Dec 02, 2008 7:26 am    Post subject: Reply with quote

Xelebes3 wrote:
The formula turns out a numerical response.


Not necessarily. If the length is NOT 3, you will have the content of B2 which is text. So, you should adjust your expectations.

David.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 02, 2008 8:00 am    Post subject: Reply with quote

It can not be done in one formula, I'm afraid. Neither TYPE nor FORMULA work in array context. A formula in a helper column can determine if a cell is text, number or error and if it has a formula or not.

Is constant text?
=AND(ISTEXT(A1) ; ISNA(FORMULA(A1))
=TYPE(A1)=2

TYPE returns 8 for all formulas.
Is numeric formula?
=AND(ISNUMBER(A1) ; TYPE(A1)=8)
=AND(ISNUMBER(A1) ; ISTEXT(FORMULA(A1))

Add-on to select all kinds of cells by content and formatting
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
hotpepper
Power User
Power User


Joined: 04 Dec 2008
Posts: 52

PostPosted: Sat Dec 06, 2008 11:08 pm    Post subject: Reply with quote

How about:

=COUNTIF(A1:A100;".*")
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group