Can I use a function in countif?

[xirx]

Hi!

The condition in COUNTIF can either be a constant,
or a logical expression, e.g.,

countif(range;"ok")
countif(range;">0")

But: Can I use a function, too? That is, if F() is
predicate (a function returing true or false), is
there anyway to do something like

countif(range;F())

I.e. I want to count the number of cells in the range
for which F returns true.

Yes, I know there are other way to do this. I just
wonder if countif can use functions for the test.

Thx 4 your replies!

 

[Bob Phillips]

No.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

This worked ok for me:

=COUNTIF(A1:A20,MONTH(TODAY())=3)

Where A1:A20 contained true/false.

 

[Arvi Laanemets]

Hi

The countif counts occurrences of second parameter in range given by first
parameter, or number of rows for which the condition string evaluates as
true for range given by first parameter. In one way or another, the
condition is checked for every value in range determined by first parameter.
As your range in column A contains True/False, and you search for True/False
too, it's OK for Excel - because the value is checked. In OP's example, the
check was made for a value too (2nd parameter is not a string, and doesn't
begin with any comparision operetor), but in check range were values of
entirely different type. When OP's range had contained Tru/False values too,
it would worked - but not in way he hoped - a number of rows would be
returned, where the function returned same value as in range, i.e. number of
pairs True:True or False:False

 

[Dave Peterson]

Ahhhh.

I misunderstood the original question.

 

[xirx]

This worked ok for me:

=COUNTIF(A1:A20,MONTH(TODAY())=3)

*g*

But today() is like a constant. What I was asking
for is: Assuming, A1:A20 contains dates and you
want to know how may of these are in March, you
could want to use:

=COUNTIF(A1:A20;month()=3)

Meaning: For any cell X in A1:A20, calculate
month(X)=3 and return the number cases in which
this equation is TRUE.

But it simiple doesn't work this way.

 

[Dave Peterson]

Yep.

Arvi showed that I misread the original question earlier.

But there are other functions that can do what you wanted to do in your example:

=SUMPRODUCT(--(MONTH(A1:A20)=3))
is one.

or this one:
=SUM(IF(MONTH(A1:A20)=3,1))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And if you're doing this for January, you have to be a little more careful.
Blank cells will be included--unless you exclude them in your formula:

=SUMPRODUCT(--(MONTH(A1:A20)=1),--ISNUMBER(A1:A20))
would work ok.

=sumproduct() likes to work with numbers. The -- converts true's and false's to
1's and 0's.