We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

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

Paijo gravatar image

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

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 +0200

karolus gravatar image

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

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 +0200 )edit

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

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 +0200 )edit

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

Lupp gravatar image

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

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 +0200 )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 +0200 )edit

Question Tools

1 follower


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

Seen: 1,047 times

Last updated: Sep 13 '14