Apache OpenOffice Community Forum

I am not sure if this formula is currently in Open Office Calc.

I am trying to count the value of rows based on parameters in two columns - i.e.

=COUNTIFS(C2:C114, "YES", F2:F114, "> 0")

I do not see this formula in the wizard. Any way to handle this>

Thanks!

=SUMPRODUCT(C2:C114="YES" ; F2:F114>0)

Perfect!

Thanks for the quick and accurate reply - this is a great forum.

You're welcome. I forgot to mention the Excel compatible variants:
=SUMPRODUCT(C2:C114="YES" * F2:F114>0)
and array formula
=SUM(C2:C114="YES" * F2:F114>0) [Ctrl+Shift+Enter]

Help me,... in Excel
=COUNTIFS($K$3:$K$19,O18,$E$3:$E$19,$P$17)
What the formula from Open Office Calc?
thanks.

akhsanfitrianto wrote:Help me,... in Excel
=COUNTIFS($K$3:$K$19,O18,$E$3:$E$19,$P$17)
What the formula from Open Office Calc?
thanks.

Help yourself! Read the first post in this thread, then the second (Villeroy's first answer). See what is repeated and what is changed. Make the same changes in your formula.

If you need this to work in both Excel and Calc, read the fourth post (Villeroy's second). The logic of that solution is a bit more elusive, but the solution is just as easy to translate to your formula.

Either way, only 3 characters need to be changed.

any way to get this to work with wildcards.

=COUNTIFS($A$1:$A$59,"*1:*", $B$1:$B$59,"1") ---excel works

=sumproduct($A$1:$A$59,"*1:*" ; $B$1:$B$59,"1") -- calc does not work with ./ before the *

You can use COUNTIFS() in OpenOffice 4.1 and maybe 4.0. Just add a . in front of the *

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. 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 should work. SEARCH is introducing the ability to work with RegEx.
You don't actually need RegEx this way and may thus use

neither one works. they both return zero.

=SUMPRODUCT(ISNUMBER(SEARCH(".*1:.*";$A$1:$A$59));$B$1:$B$59="1")

=SUMPRODUCT(ISNUMBER(FIND("1:";$A$1:$A$59));$B$1:$B$59="1")

None of the cells in column B contains the text 1, some of them contain the numeric value 1, so I changed your formula to and now the formulas work. Numbers and text are treated as separate type in Calc, much more so than in Excel.

Ah. very good, I should have caught that, thanks.

try =SUMPRODUCT((C2:C92="Saturday")+(C2:C92="Saturday"))
its working for me