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

Author Message
pampo
General User

Joined: 07 Sep 2005
Posts: 8

 Posted: Wed Nov 30, 2005 8:52 am    Post subject: SUMIF + OR + regular expressions Hello everybody, probably a stupid question, but I looked at OO Calc help and all around this forum, but I was not able to find an answer. I just need to get the sum of a range of cells, say A1:A10 when the textual content of range B1:B10 contains say "apple" or "peach". I did something like that: SUMIF(A1:A10;OR(".*apple.*";".*peach.*";B1:B10) and in this case I get #VALUE! The same if I write: SUMIF(A1:A10;OR("=.*apple.*";"=.*peach.*";B1:B10) While: SUMIF(A1:A10;"=.*apple.*";B1:B10) i.e. checking only one condition works fine, either with or without "=" Please help me, Matteo.
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Wed Nov 30, 2005 9:12 am    Post subject: Hi Matteo, =SUMIF(B1:B10;"apple";A1:A10)+SUMIF(B1:B10;"peach";A1:A10) HTH ,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
pampo
General User

Joined: 07 Sep 2005
Posts: 8

 Posted: Wed Nov 30, 2005 9:20 am    Post subject: Many thanks 8daysaweek.co.uk, but what about the OR function? I spent so much time on it that now I would also like to understand how to use it... Matteo.
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Wed Nov 30, 2005 9:24 am    Post subject: I'm not sure if you can nest an OR() function to achieve what you want, maybe. I would probably use a hidden column to give a result if your or calculation = true, then SUMIF from that e.g. (not tested) in column C of each row OR(".*apple.*";".*peach.*") then =SUMIF(C1:C10;TRUE;A1:A10) BFN ,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
richhill

Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

 Posted: Wed Nov 30, 2005 9:35 am    Post subject: Also try.. =SUMIF(B1:B10;"apple|peach";A1:A10)_________________OOo Calc tips: http://www.openofficetips.com
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Wed Nov 30, 2005 9:45 am    Post subject: Much better =SUMIF(B1:B10;".*apple.*|.*peach.*";A1:A10) also works. BFN,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
richhill

Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

 Posted: Wed Nov 30, 2005 9:47 pm    Post subject: Another solution - using array formula... {=SUM(((B1:B10="Apple")+(B1:B10="Peach"))*A1:A10)}_________________OOo Calc tips: http://www.openofficetips.com
pampo
General User

Joined: 07 Sep 2005
Posts: 8

 Posted: Thu Dec 01, 2005 8:48 am    Post subject: Many many thanks to all you guys, Matteo.
 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