CountIF with an AND condition?

[DanCalc]

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!

[Villeroy]

Search this forum for SUMPRODUCT

[acknak]

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.

[DanCalc]

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

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!

[Charlie Young]

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

=SUMPRODUCT(Data>=10;Data<20)

[gerard24]

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

COUNTIF((data>=10)*(data<20);1)

Only with Calc, impossible in Excel....

[acknak]

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.