[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Xelebes3
Power User

Joined: 30 Nov 2008
Posts: 50
Location: Canada

 Posted: Mon Dec 01, 2008 3:18 pm    Post subject: Countng text 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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668
Location: Canada

 Posted: Mon Dec 01, 2008 5:25 pm    Post subject: 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.
Xelebes3
Power User

Joined: 30 Nov 2008
Posts: 50
Location: Canada

 Posted: Mon Dec 01, 2008 6:24 pm    Post subject: Does not work. It is still counting the cells with a formula.
David
Super User

Joined: 24 Oct 2003
Posts: 5668
Location: Canada

Posted: Mon Dec 01, 2008 7:15 pm    Post subject:

 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.
Xelebes3
Power User

Joined: 30 Nov 2008
Posts: 50
Location: Canada

 Posted: Mon Dec 01, 2008 7:47 pm    Post subject: The formula turns out a numerical response. A formula like this: =IF(LEN(\$B2)=3;\$B2;"") Where the cell B2 has text.
David
Super User

Joined: 24 Oct 2003
Posts: 5668
Location: Canada

Posted: Tue Dec 02, 2008 7:26 am    Post subject:

 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue Dec 02, 2008 8:00 am    Post subject: 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
hotpepper
Power User

Joined: 04 Dec 2008
Posts: 52

 Posted: Sat Dec 06, 2008 11:08 pm    Post subject: How about: =COUNTIF(A1:A100;".*")
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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