SUMIF + OR + regular expressions

pampo · General User Joined: 07 Sep 2005 Posts: 8

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

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

pampo · General User Joined: 07 Sep 2005 Posts: 8

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

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

richhill · 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

Much better Very Happy

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

BFN,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site

richhill · 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

Many many thanks to all you guys,

Matteo.