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 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...
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 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 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 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 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 run a formula only when one or more cells are not blank, you can use the IF function with an appropriate logical criteria. In the example shown, the formula in E5 is:
=IF(COUNT(C5:C7)=3,SUM(C5:C7),"")...
Related videos
In this video, we'll look at how to use the COUNT function to count cells that contain numeric data.
In this video, we'll look at how to create a dynamic reference to a named range with the INDIRECT function. This is one way to switch ranges on the fly.
Related functions
Good links
