Count cells that contain numbers

Let's say you want to count only rows that contain numbers and dates in a column that contains a mix of different data types. There are several ways to count cells that contain numbers.

What do you want to do?

Count cells that contain numbers in a contiguous row or column

Count cells that contain numbers that are not in a contiguous row or column

Count cells that contain numbers in a contiguous row or column

1. Select a cell below or to the right of the numbers that you want to count.
2. On the Home tab, in the Editing group, click the arrow next to AutoSum , click Count Numbers, and then press ENTER.

 Top of Page

Count cells that contain numbers that are not in a contiguous row or column

To do this task, use the COUNT function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Function details

COUNT

 Top of Page

Excel > Formula and name basics > Examples of formulas > Counting

Count nonblank cells

Excel 2007

Let's say you want to find out how many people entered project hours this week or how many products sold on a specific day. To count nonblank cells, use the COUNTA function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Function details

COUNTA

Excel > Formula and name basics > Examples of formulas > Counting

Count how often a value occurs

Excel 2007

Let's say you want to find out how many times particular text or a number value occurs. For example:

• If a range contains the number values 5, 6, 7, and 6, then the number 6 occurs two times.
• If a column contains "Buchanan", "Dodsworth", "Dodsworth", and "Dodsworth", then "Dodsworth" occurs three times.

There are several ways to count how often a value occurs.

What do you want to do?

Count how often a single value occurs by using a function

Count how often multiple number values occur by using functions

Count how often multiple text or number values occur by using functions

Count how often multiple values occur by using a PivotTable report

Count how often a single value occurs by using a function

Use the COUNTIF function to perform this task.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Function details

COUNTIF

 Top of Page

Count how often multiple number values occur by using functions

Let's say you need to determine how many salespeople sold a particular item in a certain region, or you want to know how many sales over a certain value were made by a particular salesperson. You can use the IF and COUNT functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Notes 

• The formulas in the example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After copying the example to a blank worksheet, select the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.
• For these formulas to work, the second argument to the IF function must be a number.

Function details

COUNT

IF

 Top of Page

Count how often multiple text or number values occur by using functions

Use the IF and SUM functions to do this task:

• Assign a value of 1 to each true condition by using the IF function.
• Add the total, by using the SUM function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Note    The formulas in this example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.

Function details

IF

SUM

 Top of Page

Count how often multiple values occur by using a PivotTable report

You can use a PivotTable report to display totals and to count the occurrences of unique values.

1. Select the column that contains the data. Make sure that the column has a column heading.
2. On the Insert tab, in the Tables group, click PivotTable.
3. The Create PivotTable dialog box is displayed.
4. Click Select a table or range.
5. Place the PivotTable report in a new worksheet starting at cell A1 by clicking New Worksheet.
6. Click OK.

An empty PivotTable report is added to the location that you specified with the PivotTable field list displayed.

7. In the field section at the top of the PivotTable field list, click and hold the field name, and then drag the field to the Row Labels box in the layout section at the bottom of the PivotTable field list.
8. In the field section at the top of the PivotTable field list, click and hold the same field name, and then drag the field again to the Values box in the layout section at the bottom of the PivotTable Field List.

 Note    If your data contains numbers, the PivotTable report totals the entries instead of counting them. To change from the Sum summary function to the Count summary function, select a cell in that column, and then on the Options tab in the Active Field group, click Field Settings, click the Summarize by tab, click Count, and then click OK.

 Top of Page

Excel > Formula and name basics > Examples of formulas > Counting

Count unique values among duplicates

Excel 2007

Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains:

• The values 5, 6, 7, and 6, then the result is three unique values—5 , 6 and 7.
• The values "Buchanan", "Dodsworth", "Dodsworth", "Dodsworth", then the result is two unique values—"Buchanan" and "Dodsworth".

There are several ways to count unique values among duplicates.

What do you want to do?

Count the number of unique values by using a filter

Count the number of unique values by using functions

Count the number of unique values by using a filter

You can use the Advanced Filter dialog box to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.

1. Select the range of cells, or make sure the active cell is in a table.

Make sure the range of cells has a column heading.

2. On the Data tab, in the Sort & Filter group, click Advanced.

The Advanced Filter dialog box is displayed.

3. Click Copy to another location.
4. In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog .

5. Select the Unique records only check box, and click OK.

The unique values from the selected range are copied to the new location beginning with the cell you specified in the Copy to box.

6. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument, excluding the column heading. For example, if the range of unique values is B2:B45, then enter:

=ROWS(B2:B45)

 Top of Page

Count the number of unique values by using functions

Use the IF, SUM, FREQUENCY, MATCH, and LEN functions to do this task:

• Assign a value of 1 to each true condition by using the IF function.
• Add the total by using the SUM function.
• Count the number of unique values by using the FREQUENCY function. The FREQUENCY function ignores text and zero values. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a zero.
• Return the position of a text value in a range by using the MATCH function. This value returned is then used as an argument to the FREQUENCY function so that the corresponding text values can be evaluated.
• Find blank cells by using the LEN function. Blank cells have a length of 0.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 Notes 

• The formulas in this example must be entered as array formulas (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER.
• To see a function evaluated step by step, select the cell containing the formula, and then on the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

Function details

FREQUENCY

MATCH

LEN, LENB

SUM

IF

 Top of Page

Excel > Formula and name basics > Examples of formulas > Counting

Count numbers greater than or less than a number

Excel 2007

Let's say you want to count how many salespeople exceeded their sales goals for a quarter or how many stores under-performed compared to an industry average for yearly revenues. To count numbers greater than or less than a number, use the COUNTIF function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Function details

COUNTIF

Excel > Formula and name basics > Examples of formulas > Counting

Count all of the cells in a range

Excel 2007

Let's say you want to determine the size of a large spreadsheet to decide whether to use manual or automatic calculation in your workbook. To count all of the cells in a range, use a formula that multiplies the return values from the ROWS and COLUMNS functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Function details

ROWS

COLUMNS

Excel > Formula and name basics > Examples of formulas > Counting

Count the number of words in a cell or range

Excel 2007

Let's say you want to find out how many words are used in a cell or cell range that contains a long string of text, such as a description or comment field. There are several ways to do this task.

What do you want to do?

Count the number of words in a cell

Count the number of words in a range of cells

Count the number of words in a cell

To do this task, use the LEN, TRIM, and SUBSTITUTE functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

3. Press CTRL+C.
4. On the worksheet, select cell A1, and then press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

In this example, cell A1 contains the text "Have a nice day!", and cell A2 contains the formula. The formula returns a value of 4 to reflect that the cell contains four words separated by spaces. It makes no difference if words are separated by multiple spaces or if words start or end with a space. The TRIM function removes extra spaces, starting spaces, and ending spaces in the text of the cell.

 Note    To avoid a circular reference, make sure that you enter the formula in a cell outside of the cell for which you are counting the words. Also make sure that you change the cell references in the formula to reflect the cell that contains the words that you want to count.

Function details

LEN, LENB

TRIM

SUBSTITUTE

 Top of Page

Count the number of words in a range of cells

To do this task, use the SUMIF, LEN, TRIM, and SUBSTITUTE functions in an array formula.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

3. Press CTRL+C.
4. On the worksheet, select cell A1, and then press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

In this example, the formula returns a value of 37 to reflect that the range contains thirty-seven words separated by spaces. It makes no difference if words are separated by multiple spaces or if words start or end with a space. The TRIM function removes extra spaces, starting spaces, and ending spaces in the text of the cell. To get the total number of words in the specified range, the array formula counts the words in each cell of that range, and the SUM function then adds up the values for each cell to get the total.

 Notes 

• To avoid a circular reference, make sure that you enter the formula in a cell outside of the range for which you are counting the words. Make sure that you change the range references in the formula to reflect the range of cells that contain the words that you want to count.
• To enter an array formula, select the cell that contains the formula, press F2, and then press CTRL+SHIFT+ENTER.

Tip  To see a function evaluated step by step, select the cell that contains the formula. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

Function details

SUMIF

LEN, LENB

TRIM

SUBSTITUTE

 Top of Page

See Also

• Count all of the cells in a range
• Count cells that contain numbers
• COUNT function
• Count how often a value occurs
• Count nonblank cells
• Count numbers greater than or less than a number
• Count unique values among duplicates
• COUNTIF function

Excel > Formula and name basics > Examples of formulas > Counting

Calculate a running total

Excel 2007

Let's say you want to see the number of products in an inventory list grow as you add products each day. Use a running total (also called a running count) to watch the number of items in a spreadsheet add up as you enter new items. To calculate a running total, use the following procedure.

1. Set up a worksheet like the following example.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

2. Click anywhere outside cell C4 to see the running total.
3. To maintain the running total, add a row for each new entry by doing the following:
   1. Type the product and description into the empty rows directly below the existing data.

For instance, if you were using the example above, you would type the products into A5, A6, and so on, and the descriptions into B5, B6, and so on.

2. Extend the running total formula into the new rows by selecting the last cell in the balance column and then double-clicking the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.).

For instance, if you were using the example above, you would select cell C3 and then double-click its fill handle to extend the formula into all new rows that contain products and descriptions.

 Note    A running total differs from a running balance, in which you watch the sum of values grow as new entries are added. For more information, see Calculate a running balance.