| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 http://forum.openoffice.org |
|
| Back to top |
|
|
hotpepper
Power User
Joined: 04 Dec 2008
Posts: 52
|
| Posted: Sat Dec 06, 2008 11:08 pm Post subject: |
|
|
How about:
=COUNTIF(A1:A100;".*") |
|
| Back to top |
|
|
|
