CODEWITHSUNDEEP
functionsumopenoffice-calcsumifs
theHubi
theHubi

Reputation: 59

OpenOffice - Combining multiple functions - Substring

I have a combination of functions I want to program in OpenOffice to look for a specific substring. Here's what I got:

=SUMIF(C3:C130;(LEFT(C3:C130;6)) = "String"; D3:D130)

To explain in words: Make a sum if the area C3:C130 contains the string "String" in the first 6 letters of the line. The numbers for the sum are in D3:D130.

For some reason I can't think of the solution right now. I think I'm missing some sort of "IF "String" equals "String" "-function here. Could you point me in the right direction, please? Much appreciated!

edit01: I just looked up the "LEFT"-function again and apparently I have that one wrong as well.

How do I ask the function to check a line for a the value "String" as a part of a longer text?

Upvotes: 1

Views: 359

Answers (1)

tohuwawohu
tohuwawohu

Reputation: 13618

First, make sure that regular expressions are enabled for formulas: open menu Tools -> Options -> OpenOffice Calc -> Calculate -> General Calculations; check "Enable regular expressions in formulas".

Now, you can modify the formula as follows:

=SUMIF(C3:C130; "^String.*"; D3:D130)

The regular expression ^String.* matches every cell beginning with String, using ^ for positional matching. The last part of the expression .* matches every character following String if there is any.

Upvotes: 1

Related Questions