OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

SUMIF + OR + regular expressions

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
pampo
General User
General User


Joined: 07 Sep 2005
Posts: 8

PostPosted: Wed Nov 30, 2005 8:52 am    Post subject: SUMIF + OR + regular expressions Reply with quote

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.
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Wed Nov 30, 2005 9:12 am    Post subject: Reply with quote

Hi Matteo,

=SUMIF(B1:B10;"apple";A1:A10)+SUMIF(B1:B10;"peach";A1:A10)

HTH Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
pampo
General User
General User


Joined: 07 Sep 2005
Posts: 8

PostPosted: Wed Nov 30, 2005 9:20 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Wed Nov 30, 2005 9:24 am    Post subject: Reply with quote

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 Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
richhill
OOo Advocate
OOo Advocate


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

PostPosted: Wed Nov 30, 2005 9:35 am    Post subject: Reply with quote

Also try..

=SUMIF(B1:B10;"apple|peach";A1:A10)
_________________
OOo Calc tips: http://www.openofficetips.com
Back to top
View user's profile Send private message Visit poster's website
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Wed Nov 30, 2005 9:45 am    Post subject: Reply with quote

Much better Very Happy

=SUMIF(B1:B10;".*apple.*|.*peach.*";A1:A10) also works.

BFN,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
richhill
OOo Advocate
OOo Advocate


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

PostPosted: Wed Nov 30, 2005 9:47 pm    Post subject: Reply with quote

Another solution - using array formula...

{=SUM(((B1:B10="Apple")+(B1:B10="Peach"))*A1:A10)}
_________________
OOo Calc tips: http://www.openofficetips.com
Back to top
View user's profile Send private message Visit poster's website
pampo
General User
General User


Joined: 07 Sep 2005
Posts: 8

PostPosted: Thu Dec 01, 2005 8:48 am    Post subject: Reply with quote

Many many thanks to all you guys,

Matteo.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group