Monday, May 02, 2005

Using the COUNT functions in Excel

There are several COUNT functions in Excel. Here is a brief explanation of each and when to use them.

COUNT(value1,value2,...)
Use the COUNT function to count all the numbers is a range or multiple ranges. Each cell with a number in it returns a count of 1.
Value1, value2, ... are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.
COUNT only counts numbers. If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

COUNTA(value1,value2,...)
If you need to count logical values, text, or error values, use the COUNTA function.
Value1, value2, ... are 1 to 30 arguments representing the values you want to count. In this case, a value is any type of information, including empty text ("") but not including empty cells. If an argument is an array or reference, empty cells within the array or reference are ignored. If you do not need to count logical values, text, or error values, use the COUNT function.
Each non empty cell returns a count of 1.

COUNTBLANK(range)
Use COUNTBLANK to find the number of empty cells in range.
Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.

COUNTIF(range,criteria)
Use the COUNTIF function when you you want to see how many of a specific value exists in a range. Eg the number of times a name appears in a list of names.
COUNTIF counts the number of cells within a range that meet the given criteria.
The criteria is in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples".

Additional information.
Microsoft Excel provides additional functions that can be used to analyze your data based on a condition. For example, to calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function. To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.