Excel COUNT Function
Summary
The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges.
Purpose
Count numbers
Return value
A number representing a count of numbers.
Syntax
=COUNT (value1, [value2], ...)
Arguments
- value1 - An item, cell reference, or range.
- value2 - [optional] An item, cell reference, or range.
Usage notes
The COUNT function counts the number of numbers in supplied values. Values can be items, cell references, or ranges. For example, =COUNT(1, 2, "apple") returns 2. And COUNT(A1:A10) will count the number of numeric values in the range A1:A10.
- Count can handle up to 255 additional values.
- Use COUNTA to include text and logical values. Use COUNTIF to count based on criteria.
- Error values or text values that cannot be coerced into numbers are not counted
- The logical values TRUE and FALSE are not counted.
- Empty cells and text are not counted.
COUNT formula examples
To count the number of cells that are not blank, use the COUNT function. In the generic form of the formula (above) rng represents a range of cells.
In the example, the active cell contains this formula:
=COUNT(B4:B8...
To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function.
In the example shown, the formula in C5 is:
{=AND(...
To average the last 5 data values in a range of columns, you can use the AVERAGE function together with the COUNT and OFFSET functions. In the example shown, the formula in F6 is:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1...
To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. In the example shown, data validation is applied with this...
To use data validation to restrict punctuation, you can use a named range and a formula based on the FIND and COUNT functions. In the example shown, the data validation applied to C5:C10 is:
=COUNT(FIND(xlist,B5))=0...
To test if a cell (or any text string) contains a number, you can use the FIND function together with the COUNT function. In the generic form of the formula (above), A1 represents the cell you are testing. The numbers...
To dynamically sort data with both numbers and text in alphabetical order you can use a formula to generate a numeric rank in a helper column, then use INDEX and MATCH to display values based on rank. In the example...
To average the last 5 data points, you can use the AVERAGE function together with the COUNT and OFFSET functions. You can use this approach to average the last N data points: last 3 days, last 6 measurements, etc.
In...
Related functions
Good links
