Excel help for the rest of us
To count how often a value occurs within a range we use the COUNTIF function.
Because this function count cells based on a given criteria, that fact will help us limit the count to our specified value.
If in the example below we want to count how often “John” appeared in the list, then we will use the following formula:
=COUNTIF(B1:B15;”John”)
This will give us a result of 7 as you can see.
Read more on How to use the COUNTIF function.
Applies to Excel 2003
Counts the number of cells that contain numbers within a given range. It can also accept a list of arguments other than cell ranges and counts numbers within that list of arguments. Use COUNT to find out how many cells or arguments that have numeric values.
Syntax
COUNT(value1,value2,…)
Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
Syntax
COUNTA(value1,value2,…)
Counts empty cells in a specified range of cells.
Syntax
COUNTBLANK(range)
Counts the number of cells within a range that meet the given criteria.
The criteria can be a number, text, expression or a cell reference.
Syntax
COUNTIF(range,criteria)
Applies to Excel 2003
To count numbers greater than or less than a given number, you use the COUNTIF function.
As asn example if you have a list of numbers in a range of cells B1:B20 and you want to know how many of those cells have a value greater than 100, then you use COUNTIF as follows:
COUNTIF(B1:B20,”>100″).
On the other hand if you want to know how many of those cells have a negative value , then you use COUNTIF as follows:
COUNTIF(B1:B20,”<0″).
Read more about the COUNTIF function here.
Applies to Excel 2003
The COUNTIF function counts the number of cells within a given range that meet a given criteria.
Syntax
COUNTIF(range,criteria)
Range: The range of cells from which you want to count cells.
Criteria: The criteria upon which you want to base your count. It defines which cells will be counted.
The criteria can b a number, expression, cell reference, or a text.
Examples:
=COUNTIF(B15:B33;”>100″)
=COUNTIF(A1:A8;”ABC”)
The examples below show how to use COUNTIF with numeric values. A1 to A6 is our data range. In rows 7, 8 and 9 you have the text of the formula in column A (for explanation only), the actual formula in column B and column C shows which cells are qualified to be counted.
COUNTIF and wildcards
You can use the wildcard characters question mark (?) and asterisk (*) in the COUNTIF criteria. A question mark matches any single character; an asterisk matches any sequence of characters.
Examples:
The examples below show how to use COUNTIF with wildcards. A1 to A11 is our data range. In rows 12, 13, 14, 15 and 16 you have the text of the formula in column A (for explanation only), the actual formula in column B, and column C shows which cells are qualified to be counted.
Note:
If you want to find an actual question mark or asterisk, type a tilde (~) before the character.