Using OpenOffice.org 1.1.4 Spreadsheet under Windows XP, I made some entries
in one column of the format Surname,Firstname, e.g. Smythe,John. After the
last entry in the column I entered the following formula:
=COUNTIF(A1:A8;"Smythe,.*"), and the figure 2 was presented for that entry,
correctly counting the number of "Smythe" entries in the column. So it works
in Windows!
The data included:
Smythe,Becoac
Smytheson,John
Smythe,Robespeare
Changing the formula to:
=COUNTIF(A1:A8;"Smythe.*") resulted in the figure 3 appearing, because
"Smytheson", which was one of the entries in the column, was now also being
counted. So the comma in the formula matters.
Wondering if this technique would work for a dot separator rather than a
comma separator, I changed the entries in the column to have the format
Surname.Firstname, e.g. Smythe.John and changed the formula to
=COUNTIF(A1:A8;"Smythe..*"). The result here (3) was the same as when using
the formula =COUNTIF(A1:A8;"Smythe.*"). So this technique would be no use
for, say, counting parts of URIs (e.g. openoffice.org), which use dot
separators.
Regards,
Matthew Stannard
Software tester
----- Original Message -----
From: "Dan Lewis" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Saturday, June 25, 2005 11:51 AM
Subject: Re: [users] COUNTIF problem
Botom post
On Saturday 25 June 2005 04:29 am, Fred Grant wrote:
I can't get the "ANDERSON.*" example you cite to work either. It
shows zero count even though I have several but with different first
names. Does this work for you?
On Fri, 2005-06-24 at 22:31, Anthony Chilco wrote:
> Hi Fred,
> It appears that the regular expression search in countif only
> supports literal strings and not references to cells containing a
> string. In your case =COUNTIF(A1:A27;D12), where D12 contains
> "ANDERSON.*" won't work, while =COUNTIF(A1:A27;"ANDERSON.*") will.
> You could parse the column into first and last names.
> tc
>
> Fred Grant wrote:
> >I have a list of names with "LASTNAME,FIRSTNAME" format all in one
> >column. I would like to be able to count the number of last names
> > that are alike, eg ANDERSON but may have a different FIRSTNAME.
> > I can't get any wild cards to work. The help file suggests that
> > "ANDERSON.*" should do the trick but it doesn't work for me.
> >
> >Any ideas?
I just populated column A with 5 rows similar to your
"LASTNAME,FIRSTNAME" format. Three had one LASTNAME, and two had
another LASTNAME. The COUNTIF(A1:A5;"LASTNAME.*") format correctly
counted both LASTNAME's. I was using the Linux version. What we need to
see is a copy of your actual function. (Highlight the COUNTIF function
in the Input line box, copy it, and paste that into your email.) There
might be a typographical problem.
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.8.1/28 - Release Date: 24/06/2005
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
