If COUNTIFS still is not implemented in Apache OpenOffice you will have to use a workaround - and the SUMPRODUCT function can help to find one. This does not in the least mean that SUMPRODUCT is something like another name for COUNTIFS. It is actually a function of much more power and flexibility.

- Code: Select all Expand viewCollapse view
`=sumproduct($A$1:$A$59,"*1:*" ; $B$1:$B$59,"1")`

is wrong in the following ways:

1. The second parameter

is not a criterion applied to the elements of the first one.

2. The regular expresion looking for the pattern you need is ".*1:.*" . The "*" must always be applied to something described in the position left of it. The dot is meaning "any character" in this context but you may have completely different subexpressions there for which the "*" simply means "zero or more repetitions".

3. The fourth parameter again is not a criterion.

4. SUMPRODUCT cannot apply regular expressions directly

because it does not accept criteria.

SUMPRODUCT simply accepts an arbitrary number of equally dimensioned matrices of numeric values or expressions evaluating to such on its parameter positions. Equally positioned values will be multiplied and the sum of all these products will be returned.

Using SUMPRODUCT to get a workaround for COUNTIFS - or a working formula for a similar case where the criteria concept is insufficient, and then even if COUNTIFS is available - has to place expressions on the parameter positions which evaluate (under the ForceArray condition) to equally dimensioned matrices of values 1 if a specific condition is met or 0 if not. The logical values TRUE() and FALSE() are accepted as equivalent to the numbers 1 and 0 respectively. In your case the formula

- Code: Select all Expand viewCollapse view
`=SUMPRODUCT(ISNUMBER(SEARCH(".*1:.*";$A$1:$A$59));$B$1:$B$59="1")`

should work. SEARCH is introducing the ability to work with RegEx.

You don't actually need RegEx this way and may thus use

- Code: Select all Expand viewCollapse view
`=SUMPRODUCT(ISNUMBER(FIND("1:";$A$1:$A$59));$B$1:$B$59="1")`

On Windows 10: LibreOffice 5.3.2 and older versions, PortableOpenOffice 4.1.2 and older, StarOffice 5.2

---

Maybe we might! (Create a powerful UFO: United Free Office)

Lupp from München