[Solved] Is there a Calc Equivalent to COUNTIFS?

Discuss the spreadsheet application
Post Reply
darkfire.shadows
Posts: 11
Joined: Wed Apr 22, 2009 6:09 pm

[Solved] Is there a Calc Equivalent to COUNTIFS?

Post by darkfire.shadows »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is there a Calc Equivilant to COUNTIFS?

Post by Villeroy »

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

Re: Is there a Calc Equivilant to COUNTIFS?

Post by darkfire.shadows »

Perfect!

Thanks for the quick and accurate reply - this is a great forum.
OOo 3.0.X on Mac OSx Leopard
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
akhsanfitrianto
Posts: 1
Joined: Thu Feb 07, 2013 2:32 pm

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

Post by akhsanfitrianto »

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
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
shootz
Posts: 4
Joined: Sun Aug 03, 2014 2:55 am

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

Post by shootz »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post by FJCC »

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.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Post by Lupp »

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

=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

=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

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

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

Post by shootz »

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 361 times
openoffice 4 on windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post by FJCC »

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

=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.
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.
shootz
Posts: 4
Joined: Sun Aug 03, 2014 2:55 am

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

Post by shootz »

Ah. very good, I should have caught that, thanks.
openoffice 4 on windows 7
manshitu
Posts: 1
Joined: Thu Jan 28, 2016 1:42 pm

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

Post by manshitu »

try =SUMPRODUCT((C2:C92="Saturday")+(C2:C92="Saturday"))
its working for me
OpenOffice 4.1.2 on Windows 10
Post Reply