Ask Your Question

Why does SUMIF() ignore strings with parentheses? [closed]

asked 2014-09-12 09:36:13 +0100

Paijo gravatar image

updated 2016-03-03 18:48:37 +0100

Alex Kemp gravatar image

Why SUMIF() not work as expected?

SUMIF() ignore any strings with parenthesis? is this a new bug or something?

Version/OS: Libre Office Version: on Ubuntu 14.04 image description

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-03 18:48:48.341230

3 Answers

Sort by » oldest newest most voted

answered 2014-09-12 10:10:08 +0100

karolus gravatar image

updated 2014-09-12 10:35:08 +0100

Its not a bug.

Deactivate →Tools→Options→Calc→Calculate→→[]allow Regular Expressions in Formulas


edit flag offensive delete link more


Thank you. It solved now.

Paijo gravatar imagePaijo ( 2014-09-15 06:33:43 +0100 )edit

answered 2014-09-13 00:56:19 +0100

m.a.riosv gravatar image

The issue is that parentheses are part of the regular expressions, what can be done?

1- Use a formula like: =SUMPRODUCT(NOT(ISERROR(FIND(C794;$C$2:$C$789)));$D$2:$D$789), FIND() function does not use regular expressions.

2- Using SUBSTITUTE() function to replace in the second parameter of SUMIF() the parenthesis for \parenthesis ( -> \), but are needed at least a couple of SUBSTITUTE() nested, one for open and one for close parenthesis. =SUMIF($C$2:$C$789;SUBSTITUTE(SUBSTITUTE(C794;"(";"\(");")";"\)");$D$2:$D$789)

3- What Karulus has indicated.

edit flag offensive delete link more


Thank you. I solve the problem by deactivate regular expression in formula.

Paijo gravatar imagePaijo ( 2014-09-15 06:42:34 +0100 )edit

answered 2014-09-13 01:27:33 +0100

Lupp gravatar image

updated 2014-09-13 14:20:40 +0100

The array expression {=SUMIF($C$2:$C$789=$C794;TRUE();$D$2:$D$789)} provides another workaround in addition to those "mariosv" listed that looks rather lucid and can easily be adapted if needs change in details.

Unfortunately we have a situation for the moment with the versions 4.x.y where filling array formula 'Down' is broken.

edit flag offensive delete link more


Yes, we can copy and paste only to one cell. Because keyboard combination "Ctrl-Alt-Shit-Enter" gives same value to all cell.

Too much time to paste one-by-one when I actually have some lists of hundreds names.

Paijo gravatar imagePaijo ( 2014-09-15 06:45:16 +0100 )edit

If you are urgently interested I may offer a workaround fot this 'FillArrayExpressionUpOrDownMalfunction' bug saving at least part of the time.

Lupp gravatar imageLupp ( 2014-09-15 10:24:31 +0100 )edit

Question Tools

1 follower


Asked: 2014-09-12 09:36:13 +0100

Seen: 993 times

Last updated: Sep 13 '14