Ask Your Question
0

Calc: How to add flexible cell referenced "OR" conditions in SUMIFS?

asked 2020-03-10 10:25:14 +0200

Szelev71 gravatar image

updated 2020-09-08 15:25:53 +0200

Alex Kemp gravatar image

Is there any way to make this OR conditions flexible and using cell references?

=SUMIF(B1:B10; ".(foo|bar)."; A1:A10) (this solution is from here)

instead of ".(foo|bar)."

I am looking for something like C1|C2 (in this case C1="foo", C2="bar")

I Using Libre Office 6 in Ubuntu but none of my trials made results.

SUMIFS(A$2:A$200;B$2:B$200;C$3:C6;D$2:D$200;"3")

This function using only the value of C6 instead of C3|C4|C5|C6. With arrays I found also no better result. Any hint appreciated.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2020-03-10 10:36:20 +0200

Use

=SUMIF(B1:B10; ".*(\Q" & C1 & "\E|\Q" & C2 & "\E).*"; A1:A10)

If you need to use a range in the condition:

=SUMIF(B1:B10; ".*(\Q" & TEXTJOIN("\E|\Q";1;C1:C2) & "\E).*"; A1:A10)
edit flag offensive delete link more

Comments

amazing knowledge! :) Thanks

Szelev71 gravatar imageSzelev71 ( 2020-03-10 11:02:52 +0200 )edit

Both versions working generally.

If the condition is a number (representing months) like 2017,1 (2017 Nov) in this case the result contains also the items belong to 2017,11.

If I refer to a single cell C8 for example where 2017,1 is as a number than the result is ok.

If I use the above ways and referring to the same cell ...& C8 & "\E|\Q" & C8.... or ...E|\Q";1;C8:C8)... the result includes also the items of 2017,11

Is it some trick to avoid this?

Szelev71 gravatar imageSzelev71 ( 2020-03-10 15:31:18 +0200 )edit

If you need exact match, not "any cell that has that string somewhere inside its text", then remove the .* from both sides of the regex.

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-10 16:18:52 +0200 )edit

ohh yes! Great thank you!

Szelev71 gravatar imageSzelev71 ( 2020-03-10 22:12:06 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-03-10 10:25:14 +0200

Seen: 46 times

Last updated: Mar 10