Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What syntax allows the condition in OpenOffice's SUMIF function to match a regular expression in a different cell?

For example, suppose the cell A1 contains a regular expression. How must the question marks in the example below be replaced so that the condition for summing up uses the regex in A1?

SUMIF(A23:A240,??,B23:B240)
share|improve this question

1 Answer 1

up vote 2 down vote accepted

SUMIF(test_range; condition; sum_range) supports regular expressions if you have enabled that option. See Regular expressions in Calc

Enabling Regular Expressions in Calc

If the cell A1 contains a regular expression, then you can simply use:

SUMIF(A23:A240, A1, B23:B240)

The condition parameter can be cell reference, a number or string to match or an expression to match its value. It can also be a string that starts with a comparison operator.

SUMIF(A23:A240, "<0", B23:B240)

would sum the numbers in the second range for corresponding cells in the first range that are less than zero.

SUMIF(A23:A240, "truck", B23:B240)

would sum the numbers in the second range for corresponding cells in the first range that are less than zero.

Please see Conditional Counting and Summation in Calc for more information.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.