[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, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17246
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, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17246
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.
OOo 3.3.0 and Apache OOo 3.4.1/4.0.0, on Ms Windows 7 and Mac OS-X.
User avatar
keme
Volunteer
 
Posts: 1601
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests