| View previous topic :: View next topic |
| Author |
Message |
Raypalmer
General User
Joined: 13 Aug 2005
Posts: 10
|
 Posted: Sat Nov 26, 2005 1:21 am Post subject: How do I count letters rather than numbers |
 |
|
| I'm new to this, please help. I'm trying to count the number of times the letter 'w' appears in a column. I can get calc to work with numbers but not letters, Do I use Count? I've tried =counta(d4:28;""w"") which i think is logical but obviously doesnt work |
|
| Back to top |
|
 |
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Sat Nov 26, 2005 1:56 am Post subject: |
|
|
Hi Raypalmer,
Try COUNTIF()
BFN  ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
Raypalmer
General User
Joined: 13 Aug 2005
Posts: 10
|
| Posted: Sat Nov 26, 2005 8:29 am Post subject: How do I count letters rather than numbers |
|
|
| Cant get that to work either. I fell it must be something to do with the quotes around the letter I'm trying to count. I've tried single quotes, double and no quotes, still cant get it to work |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8982
Location: Lexinton, Kentucky, USA
|
| Posted: Sat Nov 26, 2005 9:19 am Post subject: |
|
|
This will count the number of rows in the column that contain a "w" but if the row contains more than one "w" it will only be counted as 1, e.g., "wayward" will return 1 in the total count.
=COUNTIF(A1:A6;".*W.*")
".*" is a regular expression that means any characters or no characters. |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sat Nov 26, 2005 12:24 pm Post subject: |
|
|
Now I'm totally confused. Tell me it's not my age. 
I thought I'd fiddle aroud with this since it looks interesting. So, I put "Wayward", no quotes into cell A1. In B1 I tried =COUNTA(A1;"w")
Voila! ...Oops! not quite. I put other words and letteres into more column A cells and then copied the formula down column B. I still got the value "2" I did in the first try! ...even when some A cells were empty!!
What is being calculated here?
David. |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8982
Location: Lexinton, Kentucky, USA
|
| Posted: Sat Nov 26, 2005 1:59 pm Post subject: |
|
|
David,
You are using CountA which, when viewed in Help, seems a strange function. It returns the number of values in a list so =COUNTA(1;"a";"w";3) returns 4 because we have 4 values in the list we supplied between the parentheses which seems usless unless our list was really long. If you look at your formula it returns 2 because you have supplied a list of 2 values being "A1" and "w", or copy to the next cell down, "A2" and "w".
However help indicates that we can also supply an array or reference instead of the actual values.
So put anything in cells A1-A3, skip A4 and put anything in A5 and A6. In B1 put the formula =COUNTA(A1:A6) and it will return 5 telling us that there is some type of value in 5 out of the 6 cells we gave as an array(?). I guess a "referenrce" would be the name assigned to a group of cells.
I'm glad you asked this as it made me go figure out just what COUNTA did.
I presume you noticed that the formula I gave used CountIF instead of CountA. |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sat Nov 26, 2005 8:40 pm Post subject: |
|
|
| JohnV wrote: | | I presume you noticed that the formula I gave used CountIF instead of CountA. |
Thanks. I did. I was just playing around with it, but then had to go out. Just back from an awesome dosplay of opera in a local concert series. If I have to come back again, I want to come back with a voice, and could care less if I didn't know how to turn on a computer.
Thanks again!
David. |
|
| Back to top |
|
|
Raypalmer
General User
Joined: 13 Aug 2005
Posts: 10
|
| Posted: Sun Nov 27, 2005 12:23 am Post subject: How do I count letters rather than numbers |
|
|
Sorry but I'm still confused. I can get the formula to count the number of 1's,2's 3's etc in a column using for example =countif(a1:a100;1) but if I replace the one with a it doesnt work. What I am trying to do is get the formula to count the number of (W)ins (D)raws and (L)osses in a list of results for a football team. If I replace W,D, L with numbers eg 1,2,3, it works. Its driving me barmy.
Losing sanity |
|
| Back to top |
|
|
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Sun Nov 27, 2005 1:21 am Post subject: |
|
|
Hi Raypalmer,
Try
=COUNTIF(A$1:A$100;"w.*")
HTH ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
Raypalmer
General User
Joined: 13 Aug 2005
Posts: 10
|
| Posted: Sun Nov 27, 2005 1:34 am Post subject: How do I count letters rather than numbers |
|
|
| Thanks for replying so quickly but unfortunateky that does not work either I get a zero answer rather then the 5 I should get. Cant believe this is so difficult. I've worked around this by giving each letter a numeric value in a separate column and used my formula on this new column. It means typing data in twice but thats life. So unless you can come up with any other suggestions I guess I'm stuck with it. Appreciate your help |
|
| Back to top |
|
|
8daysaweek.co.uk
Super User
Joined: 29 Nov 2003
Posts: 2130
Location: UK
|
| Posted: Sun Nov 27, 2005 5:50 am Post subject: |
|
|
Hi Raypalmer,
The above WFM, I tested on a column of text containing various Win, Loss and Draw entries.
Did you copy and paste the above or retype it? I can only assume that there's something about the way you're entering the formula, or perhaps some strange configuration setting that's preventing it from working correctly for you.
Perhaps you could upload a copy of your file on our forums http://www.8daysaweek.co.uk/forums/ then we could have a look at what the problem is?
BFN ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sun Nov 27, 2005 7:23 am Post subject: |
|
|
| 8daysaweek.co.uk wrote: |
Did you copy and paste the above or retype it? |
I'm on the way out, aand will be looking at this interesting problem later, but for now, I DID copy/paste, and it works, as do others, but does not include "Wayward", as had been already suggested. Sorry for the rushed reply, but I'll be innerested in other results later on. LAte night opera last night, out on business today, and then a Chopin concert this afternoon. Not bad for being retired in a rural area.
David. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Nov 27, 2005 8:56 am Post subject: |
|
|
The formula provided by 8daysaweek.co.uk should work if the option "Enable Regular Expressions in Formals" is checked.
See Tools > Options > Calc > Calculation |
|
| Back to top |
|
|
Raypalmer
General User
Joined: 13 Aug 2005
Posts: 10
|
| Posted: Sun Nov 27, 2005 9:34 am Post subject: |
|
|
| Thanks guys, you've saved my sanity. Following Villeroys suggestion about amending ''enable Regular Expressions in Formals'' (whatever that means) my formula now works. Thanks everyone for your patience |
|
| Back to top |
|
|
David
Super User
Joined: 24 Oct 2003
Posts: 5668
Location: Canada
|
| Posted: Sun Nov 27, 2005 10:07 am Post subject: |
|
|
| Villeroy wrote: | The formula provided by 8daysaweek.co.uk should work if the option "Enable Regular Expressions in Formals" is checked.
See Tools > Options > Calc > Calculation |
Thanks.
David. |
|
| Back to top |
|
|
|
