CountIF with an AND condition?

Discuss the spreadsheet application

CountIF with an AND condition?

Postby DanCalc » Fri Jan 14, 2011 8:07 am

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?

Postby Villeroy » Fri Jan 14, 2011 8:54 am

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

Re: CountIF with an AND condition?

Postby acknak » Fri Jan 14, 2011 3:47 pm

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.
AOO/LO 4 • Linux • Fedora 21
User avatar
acknak
Moderator
 
Posts: 20482
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: CountIF with an AND condition?

Postby DanCalc » Sun Jan 16, 2011 9:06 am

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.0000
11.0000
12.0000
13.0000
14.0000
15.0000
16.0000
17.0000
18.0000
19.0000
20.0000
21.0000
22.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?

Postby Charlie Young » Sun Jan 16, 2011 10:48 am

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
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: CountIF with an AND condition?

Postby gerard24 » Sun Jan 16, 2011 11:15 am

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.... :D
LibreOffice 4.4.5 on Windows Vista
gerard24
Volunteer
 
Posts: 730
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: CountIF with an AND condition?

Postby acknak » Sun Jan 16, 2011 4:16 pm

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.
AOO/LO 4 • Linux • Fedora 21
User avatar
acknak
Moderator
 
Posts: 20482
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests