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

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
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
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
JohnV

Joined: 07 Mar 2003
Posts: 9128
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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 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.
JohnV

Joined: 07 Mar 2003
Posts: 9128
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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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

Joined: 24 Oct 2003
Posts: 5668

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

Joined: 04 Oct 2004
Posts: 10081
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
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
David
Super User

Joined: 24 Oct 2003
Posts: 5668

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.
 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