On Sat, 2005-06-25 at 11:34, Ennio-Sr wrote: > Hi Dan, > I'm answering Matthew's post as I skipped your original message. > > * Matthew Stannard <[EMAIL PROTECTED]> [250605, 13:26]: > > 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! > > [...] > > > > And GNU/Linux does no worse than that ... ;) > > To make COUNTIF work it is necessary to do: > Tools > Options > Spreadsheet > Calculate and tick the 'Enable regular > expressions in formulas' small box. > Once done that, you can search: > "Smythe.*" > ".myt.*" > etc., etc. according to the regular expressions rules (see Help for a > full description of RE. > > > > > ----- Original Message ----- > > From: "Dan Lewis" <[EMAIL PROTECTED]> > > To: <users@openoffice.org> > > 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 > > >>> [...] > > No, after the above mentioned ticking, < =COUNTIF(A1:A27;D12) > will > also work, provided you put < ANDERSON.* > (without quotation > marks) in D12. > > HTH > Regards, > Ennio > > [Testing done with OOo-1.1.4 under Debian/Sarge]
Thanks to all. I enabled regular expressions and it works. Using the comma in the criteria field is also a good idea to insure accuracy. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]