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: <[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 > >>> [...] 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] -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (as Henry Miller used to say) ] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
