Countif function

  • Thread starter Thread starter Pieter
  • Start date Start date
P

Pieter

The countif function requires a range, but hen only
allowes for one criteria. What should one do when you
want to specify more than one criteria?
 
Hi
one way: use SUMPRODUCT. e.g. if you want to check column A AND B use
=SUMPRODUCT((A1:A100="crit_1")*(B1:B100="crit_2"))

or as alternative syntax:
=SUMPRODUCT(--(A1:A100="crit_1"),--(B1:B100="crit_2"))
 
You should use SUMPRODUCT like

=SUMPRODUCT((A1:A10="Dog")*(B1:B10="small"))

note the arrays (number of rows) needs to be the same.
 
A little add-on to what Frank and Dave wrote ..

Entire column references, for example: A:A, B:B, C:C etc
cannot be used in SUMPRODUCT
 
COUNTIF is not limited to only one criterion; perhaps you could give an
example of what you require.

Alan Beban
 
COUNTIF is not limited to only one criterion; perhaps you could give an
example of what you require. ...
Click to expand...

I believe what Alan means is that COUNTIF can accept an array as second
argument, each entry of which would be used as a separate criterion. This means
COUNTIF would seem to apply each of these criteria in parallel. That means if
the OP wants to count all matches for "apple" and "banana" in SomeRange, the OP
could use

=SUM(COUNTIF(SomeRange,{"apple","banana"}))

However, this only works when what is sought is the total number of matches for
a set of mutually exclusive (Alan: do you need another explanation of this
concept?) criteria. If the criteria aren't mutually exclusive, e.g.,
{">2","<5"}, there's a possibility that some cells in the range would satisfy
both criteria, in which case the cell would be counted twice. This isn't often a
problem counting numbers, but it can be a problem counting cells that contain
specified text IN PART, e.g., {"foo*","*bar"}.

COUNTIF does *NOT* directly support satisfying multiple criteria for each
record, e.g., counting all dates in a range falling in a particular period, such
as

=SUMPRODUCT((DateRange>DATE(2004,3,31))*(DateRange<=DATE(2004,6,30))

though I suppose one could use

=COUNTIF(DateRange,">"&DATE(2004,3,31))-COUNTIF(DateRange,">"&DATE(2004,6,30))

or

=MMULT(COUNTIF(DateRange,{">",">"}&DATE(2004,{3,6},{31,30})),{1;-1})

I suppose this is a form of applying multiple criteria to each record using
COUNTIF. Semantics.
 
Back
Top