|
 |
|
|
 |
  |
 |
 |
|
 |
|
|
|
The
COUNTIF() function returns the total
number of values in range
based on specific criteria.
- The syntax for
the function is COUNTIF (range
, criteria).
The
argument for the function "range" can
be a series of up to 255 values, a
referenced range of cells, an array
or a named range. In addition the function
can accepts logical values when typed
in directly.
The argument "criteria"
can be a number, expression (equation),
a cell reference, or text describing
which cells are to be added by the function.
e.g. 11 , "11"
, "< 15", A2 or "oranges".
The COUNTIF
function checks elements in the "range"
to see if they meet the conditions
specified in "criteria".
If the conditions are met the number
of elements are counted and returned
to the cell.
- For
example: COUNTIF({3,8,4,6,2,5,7},"<4")
= 3.
This
is beacuse the elements in the "range" "3" , "4" and "2" are
the only items that meet the criteria
of being less than 5. Upon counting
we can see there are a total of 1+1+1=
3 terms that meet the criteria.
|
How
to use the COUNTIF() function:
- Type " =COUNTIF( ".
- Enter
the reference
coordinates for the "range", "B2:B8".
- Type
a comma.
- Enter
the cell reference for
the "criteria", "C2".
- Type " ) " then
press the "Enter" key.
|
|
|
|
Note: Referenced
cells or array elements that are empty,
contain text or logical values are ignored
by the COUNTIF function. However cells
with a value of zero are included in the
calculation.
Wildcard characters can be used to extend
the matching possibilities of the "criteria".
- For example
the ? can be used to find any single
character: COUNTIF({"red",
"brown", "blue", "yellow"}, "?w")
= 2 as the words in the range "brown"
and "yellow"
both contain the letter w.
- Or an *
can be used to match a sequence
of characters: COUNTIF({"red","brown",
"blue", "yellow"}, "*wn")
= 1 as the only word that has the
letter string "wn"
in it is brown.
How
to use the COUNTIF() function
with wildcards:
- Type " =COUNTIF( ".
- Enter
the reference coordinates
for the "range", "B2:B8".
- Type
a comma.
- Enter
the "criteria"
, "*ge".
- Type " ) " then
press the "Enter" key.
|
|
|
Note: If
you want to use and actual question
mark or asterisk as selection criteria
simply add a tide (~) before the character.
e.g COUNTIF(A2:A10, "~?").
In addition to wild cards the COUNTIF
function can use a variety of functions
available in Excel to make the selection
criteria more sophisticated. For example:
COUNTIF(A2;A10, TODAY()) returns
the numer of values that have the same
date as today.
How
to use the COUNTIF() function
with other functions:
- Type " =COUNTIF( ".
- Enter
the reference coordinates
for the "range", "B2:B8".
- Type
a comma.
- Enter
the "criteria"
, "TODAY()".
- Type " ) " then
press the "Enter" key.
|
|
|
The COUNTIF function can be made to
sum value when one criteria OR another
criteria is met for example: Typing
this in to a cell "=COUNTIF(A2;A10, "orange"),
=COUNTIF(A2;A10, "apple")"
will cause the function to return a
total number of elements that are
either "apples" or "oranges".
See [How to count numnbers greater
than or less than a number]
To
meet multiple conditions at the same
time, that is to count only when criteria
1 AND criteria 2 are
met Use ""=COUNTIF(A2;A10,
>250) -COUNTIF(A2;A10, <1000)".
See [How to count unique numbers among
duplicates].
[The
COUNT function knowledgebase]
[The
SUM function knowledgebase]
[The
SUMIF function knowledgebase]
[The
SUMIFS function knowledgebase] |
|
 |
 |
 |
|
