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

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.


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.


=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)

amazing knowledge! :slight_smile: Thanks

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?

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.

ohh yes! Great thank you!