[Solved] Is there a Calc Equivalent to COUNTIFS?
-
- Posts: 11
- Joined: Wed Apr 22, 2009 6:09 pm
[Solved] Is there a Calc Equivalent to COUNTIFS?
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!
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!
Last edited by darkfire.shadows on Wed Apr 22, 2009 11:30 pm, edited 1 time in total.
OOo 3.0.X on Mac OSx Leopard
Re: Is there a Calc Equivilant to COUNTIFS?
=SUMPRODUCT(C2:C114="YES" ; F2:F114>0)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 11
- Joined: Wed Apr 22, 2009 6:09 pm
Re: Is there a Calc Equivilant to COUNTIFS?
Perfect!
Thanks for the quick and accurate reply - this is a great forum.
Thanks for the quick and accurate reply - this is a great forum.
OOo 3.0.X on Mac OSx Leopard
Re: [SOLVED]Is there a Calc Equivilant to COUNTIFS?
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]
=SUMPRODUCT(C2:C114="YES" * F2:F114>0)
and array formula
=SUM(C2:C114="YES" * F2:F114>0) [Ctrl+Shift+Enter]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 1
- Joined: Thu Feb 07, 2013 2:32 pm
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
Help me,... in Excel
=COUNTIFS($K$3:$K$19,O18,$E$3:$E$19,$P$17)
What the formula from Open Office Calc?
thanks.
=COUNTIFS($K$3:$K$19,O18,$E$3:$E$19,$P$17)
What the formula from Open Office Calc?
thanks.
OpenOffice 3.1 on Windows 7
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
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.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.
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.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
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 *
=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 *
openoffice 4 on windows 7
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
You can use COUNTIFS() in OpenOffice 4.1 and maybe 4.0. Just add a . in front of the *
Code: Select all
=COUNTIFS($A$1:$A$59,".*1:.*", $B$1:$B$59,"1")
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
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
Code: Select all
=sumproduct($A$1:$A$59,"*1:*" ; $B$1:$B$59,"1")
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
=SUMPRODUCT(ISNUMBER(SEARCH(".*1:.*";$A$1:$A$59));$B$1:$B$59="1")
You don't actually need RegEx this way and may thus use
Code: Select all
=SUMPRODUCT(ISNUMBER(FIND("1:";$A$1:$A$59));$B$1:$B$59="1")
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
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")
=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")
- Attachments
-
- sumprod.ods
- (17.24 KiB) Downloaded 365 times
openoffice 4 on windows 7
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
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.
Code: Select all
=SUMPRODUCT(ISNUMBER(SEARCH(".*1:.*";$A$1:$A$59));$B$1:$B$59=1)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
Ah. very good, I should have caught that, thanks.
openoffice 4 on windows 7
Re: [Solved] Is there a Calc Equivalent to COUNTIFS?
try =SUMPRODUCT((C2:C92="Saturday")+(C2:C92="Saturday"))
its working for me
its working for me
OpenOffice 4.1.2 on Windows 10