Ask Your Question
0

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: 4.3.1.2 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
0

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

Karolus

edit flag offensive delete link more

Comments

Thank you. It solved now.

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

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

Comments

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
2

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

Comments

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

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

Question Tools

1 follower

Stats

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

Seen: 752 times

Last updated: Sep 13 '14