Countif Function - mulitple criterion

  • Thread starter Thread starter Joseph M. Yonek
  • Start date Start date
J

Joseph M. Yonek

I am trying to use the countif function to count selected items using
multiple criterion. I have tried using the and/or functions and have failed
in my attempts.

Does anyone have any insights?

Thanks for your time.
Joe
 
COUNTIF can only take one criterion. For multiple criteria, use
SUMPRODUCT():

=SUMPRODUCT(--(rng1=criterion1),--(rng2=criterion2),...)
 
The two arguments to COUNTIF are range and criteria. It will not accept
multiple ranges; it *will* accept multiple criteria.
=COUNTIF(rng1,{criterion1,criterion2}), array entered, or
=SUM(COUNTIF(rng1,{criterion1,criterion2})) entered normally, both work.

J.E. McGimpsey's response may be correct for your application if you
meant something other than just multiple criteria--like multiple ranges
with a different criterion (or even the same criterion) for each range,
as he assumed.

Alan Beban
 
Hi Joseph,
If you find JEMcG use of double unitary operators too esoteric for your
taste then use

=SUMPRODUCT((rng1=criterion1)*(rng2=criterion2)*...)

But I expect the double negation has hidden merit (speedier?) or JEMcG would
not have suggested it.
Bernard
 
Alan Beban said:
The two arguments to COUNTIF are range and criteria. It will not accept
multiple ranges; it *will* accept multiple criteria.
=COUNTIF(rng1,{criterion1,criterion2}), array entered, or
=SUM(COUNTIF(rng1,{criterion1,criterion2})) entered normally, both work.

J.E. McGimpsey's response may be correct for your application if you
meant something other than just multiple criteria--like multiple ranges
with a different criterion (or even the same criterion) for each range,
as he assumed.
Click to expand...

The caveat is that criterion1 and criterion2 are constants, not cell refs.

"=COUNTIF(rng1,{criterion1,criterion2}), array entered" cannot produce the
desired result, unless you sum what it returns. The latter formula is the
one that should be used if variablization is not required.
 
Thanks for the correction, Alan. For some reason I was thinking of
two different range-criterion pairs (and didn't get the wording
right even for that).
 
Aladin said:
The caveat is that criterion1 and criterion2 are constants, not cell refs.

Correct.

"=COUNTIF(rng1,{criterion1,criterion2}), array entered" cannot produce the
desired result, unless you sum what it returns. The latter formula is the
one that should be used if variablization is not required.
Click to expand...

Well, that presumes too much about what the desired result is. One might
simply want the array of results for multiple criteria, whether that's
to sum it now or later, find the maximum or minimum now or later, take
the average now or later, simply view it now or later, etc., etc.

Alan Beban
 
The two arguments to COUNTIF are range and criteria. It will not accept
multiple ranges; it *will* accept multiple criteria.
=COUNTIF(rng1,{criterion1,criterion2}), array entered, or
=SUM(COUNTIF(rng1,{criterion1,criterion2})) entered normally, both work.
Click to expand...

Ah, semantics. =COUNTIF(X,{y,z}) applies criteria y and z separately to range X,
producing two separate results. Only in cases where no more than one of those
criteria could be satisfied for each cell in X would this approximate what most
English speaking people would consider multiple criteria - in that limited
context, the sum of the result would be the number of cells in range X
satisfying either y or z. E.g., =SUM(COUNTIF(X,{"<0",">0"})) returns the count
of nonzero numbers in range X. When the conditions aren't mutually exclusive
(for Alan's benefit, that means that the same cell in X could satisfy multiple
criteria), the result isn't useful except as the equivalent of two separate
COUNTIF calls. E.g., =COUNTIF(X,{"<=0";">=0"}) counts zeros in both returned
values, so the sum would double count any zeros in range X.

In general, multiple AND criteria should be expressed as

=SUMPRODUCT(--(X=y),--(X=z))

and *robust* OR criteria as

=SUMPRODUCT(--((X=y)+(X=z)>0))
 
I can't seem to make this work

=SUMPRODUCT(--('List Load Dates'!D:D=37897),--('List Load Dates'!D:D=37897))

What am I doing wrong?

I tried to set the numbers the same in this example, but in the actual
situation I am going to be have >= and <= to set a range.

Is there a way to do this with cell references?

Thanks again for all of your help.

Joe
 
Like all other functions using arrays, you can't use an entire
column or row. Try:

=SUMPRODUCT(--('List Load Dates'!D1:D65535=x),--('List Load
Dates'!D1:D65535=y))

you can make the range smaller to suit, or make it dynamic by giving
it a name (assuming no blanks):

Insert/Name/Define

Name: my_rng
RefersTo: =OFFSET('List Load Dates'!$D$1,,,COUNTA('List Load
Dates'!$D:$D),1)

then use

=SUMPRODUCT(--(my_rng=x),--(my_rng=y))
 
Any one help?, I need help using any formula to beable to look in one
row and if it is the correct time going to the next row over or any
other row and count the number of alarms.

So if I am looking for the number of alarms at one o'clock.. Please
help thank you for your time..

Michael
 
Back
Top