## CountIF with an AND condition?

### CountIF with an AND condition?

I'm trying to figure out how to do a CountIF with an AND condition.

What I've tried:
=COUNTIF(Data,AND(>=10,<20))
=COUNTIF(Data,AND(Data>=10,Data<20))

My data range, as you have perhaps inferred, is called: Data. I know that part works because Count(Data) yields an error-free, believable value.

Thanks!
OpenOffice 3.2.0 on VectorLinux 6.0 SOHO (Slackware 12.1.0)
DanCalc

Posts: 2
Joined: Fri Jan 14, 2011 7:57 am

### Re: CountIF with an AND condition?

Search this forum for SUMPRODUCT
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: 25359
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: CountIF with an AND condition?

Not ignoring Villeroy's wise advice, something like =SUMPRODUCT(Data;Data>=10;Data<20) should do it.

The formula loops over each row of the Data range, substituting each value ("v") into the expression: v * v>=0 * v<20. The comparisons will evaluate to one if true and zero if false, all of which produces v if both conditions are true, otherwise zero. The final sum will include only the values where both conditions are true; all the other rows contribute zeros to the sum.
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

### Re: CountIF with an AND condition?

Thanks for the help. Sorry for the late reply. I must have not turned on email notification.

I tried SUMPRODUCT(), but it yielded a logic error.

Example. A range, named "Data", of the following:
Code: Select all   Expand viewCollapse view
`10.000011.000012.000013.000014.000015.000016.000017.000018.000019.000020.000021.000022.0000`

Human count of the tens: 10 (=SUMPRODUCT(Data;Data>=10;Data<20) yields 145, =COUNTIF(Data;AND(Data>=10;Data<20)) yeilds 0)
Human count of the twenties: 3

I believe COUNTIF(Data,AND(>=10,<20)) would work in Excel, but Calc doesn't like this syntax. But it doesn't like the logic either: =COUNTIF(Data;AND(Data>=10;Data<20)) yielded 0. Sure, I could sort the actual range of data I'm working with and just use the Count() function (or just use Excel), but I'm trying to get to know Calc better.

Thanks!
OpenOffice 3.2.0 on VectorLinux 6.0 SOHO (Slackware 12.1.0)
DanCalc

Posts: 2
Joined: Fri Jan 14, 2011 7:57 am

### Re: CountIF with an AND condition?

I think the problem is that the first reference to "Data" alone in SUMPRODUCT turns it into an analog for SUMIF instead of COUNTIF. Just do

Code: Select all   Expand viewCollapse view
`=SUMPRODUCT(Data>=10;Data<20)`
Apache OpenOffice 4.1.1
Windows XP

Charlie Young
Volunteer

Posts: 1559
Joined: Fri May 14, 2010 1:07 am

### Re: CountIF with an AND condition?

DanCalc wrote:I believe COUNTIF(Data,AND(>=10,<20)) would work in Excel, but Calc doesn't like this syntax. But it doesn't like the logic either: =COUNTIF(Data;AND(Data>=10;Data<20)) yielded 0. Sure, I could sort the actual range of data I'm working with and just use the Count() function (or just use Excel), but I'm trying to get to know Calc better.

COUNTIF(Data,AND(>=10,<20)) don't work in Excel also.
SUMPRODUCT is the best function with 2 or more conditions.(See Charlie post above)

but it is possible using COUNTIF with more than one condition (this formula must be validate with ctrl+shift+enter)
Code: Select all   Expand viewCollapse view
`COUNTIF((data>=10)*(data<20);1)`

Only with Calc, impossible in Excel....
LibreOffice 5.3.7 on Windows Vista
gerard24
Volunteer

Posts: 920
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

### Re: CountIF with an AND condition?

Human count of the tens: 10 (=SUMPRODUCT(Data;Data>=10;Data<20) yields 145, =COUNTIF(Data;AND(Data>=10;Data<20)) yeilds 0)
Human count of the twenties: 3

Sorry, that's my fault. I thought you wanted a sum, not a count.

Charlie has the right solution for a count.
AOO4/LO5 • Linux • Fedora 23

acknak
Moderator

Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3