## [Solved] Is there a Calc Equivalent to COUNTIFS?

### [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!
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

Posts: 11
Joined: Wed Apr 22, 2009 6:09 pm

### 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 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy
Volunteer

Posts: 24253
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Is there a Calc Equivilant to COUNTIFS?

Perfect!

Thanks for the quick and accurate reply - this is a great forum.
OOo 3.0.X on Mac OSx Leopard

Posts: 11
Joined: Wed Apr 22, 2009 6:09 pm

### 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]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x

Villeroy
Volunteer

Posts: 24253
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### 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.
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?

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 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14

keme
Volunteer

Posts: 2571
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### 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 *
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?

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
FJCC
Moderator

Posts: 5991
Joined: Sat Nov 08, 2008 8:08 pm

### 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.
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 10: LibreOffice 5.4.1 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München

Lupp
Volunteer

Posts: 1385
Joined: Sat May 31, 2014 7:05 pm

### 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")
Attachments
sumprod.ods
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?

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
FJCC
Moderator

Posts: 5991
Joined: Sat Nov 08, 2008 8:08 pm

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

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

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

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

Posts: 1
Joined: Thu Jan 28, 2016 1:42 pm