[Solved] Is there a Calc Equivalent to COUNTIFS?

Discuss the spreadsheet application

[Solved] Is there a Calc Equivalent to COUNTIFS?

Postby darkfire.shadows » Wed Apr 22, 2009 11:09 pm

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!
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
darkfire.shadows
 
Posts: 11
Joined: Wed Apr 22, 2009 6:09 pm

Re: Is there a Calc Equivilant to COUNTIFS?

Postby Villeroy » Wed Apr 22, 2009 11:11 pm

=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 12.04, OpenOffice 4.x & LibreOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 20399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is there a Calc Equivilant to COUNTIFS?

Postby darkfire.shadows » Wed Apr 22, 2009 11:29 pm

Perfect!

Thanks for the quick and accurate reply - this is a great forum.
OOo 3.0.X on Mac OSx Leopard
darkfire.shadows
 
Posts: 11
Joined: Wed Apr 22, 2009 6:09 pm

Re: [SOLVED]Is there a Calc Equivilant to COUNTIFS?

Postby Villeroy » Wed Apr 22, 2009 11:33 pm

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]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, OpenOffice 4.x & LibreOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 20399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby akhsanfitrianto » Thu Feb 07, 2013 2:38 pm

Help me,... in Excel
=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
akhsanfitrianto
 
Posts: 1
Joined: Thu Feb 07, 2013 2:32 pm

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby keme » Thu Feb 07, 2013 3:28 pm

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.
Apache OO 3.4.1/4.1.1, on Ms Windows 7/8 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1835
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby shootz » Tue Feb 24, 2015 8:22 pm

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 *
openoffice 4 on windows 7
shootz
 
Posts: 4
Joined: Sun Aug 03, 2014 2:55 am

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby FJCC » Tue Feb 24, 2015 8:54 pm

You can use COUNTIFS() in OpenOffice 4.1 and maybe 4.0. Just add a . in front of the *
Code: Select all   Expand viewCollapse view
=COUNTIFS($A$1:$A$59,".*1:.*", $B$1:$B$59,"1")
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 4711
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby Lupp » Tue Feb 24, 2015 9:12 pm

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 8.1: LibreOffice 4.4.4, PortableOpenOffice 4.1.1, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
User avatar
Lupp
 
Posts: 518
Joined: Sat May 31, 2014 7:05 pm

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby shootz » Wed Feb 25, 2015 5:42 am

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")
Attachments
sumprod.ods
(17.24 KiB) Downloaded 54 times
openoffice 4 on windows 7
shootz
 
Posts: 4
Joined: Sun Aug 03, 2014 2:55 am

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby FJCC » Wed Feb 25, 2015 5:53 am

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
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(ISNUMBER(SEARCH(".*1:.*";$A$1:$A$59));$B$1:$B$59=1)

and now the formulas work. Numbers and text are treated as separate type in Calc, much more so than in Excel.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 4711
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Is there a Calc Equivalent to COUNTIFS?

Postby shootz » Wed Feb 25, 2015 6:05 am

Ah. very good, I should have caught that, thanks.
openoffice 4 on windows 7
shootz
 
Posts: 4
Joined: Sun Aug 03, 2014 2:55 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 34 guests