Ways to count values in a worksheet

Counting versus summing Counting is an integral part of data analysis, whether you are tallying the head count of a department in your organization or the number of units that were sold quarter-by-quarter. Excel provides multiple techniques that you can use to count cells, rows, or columns of data.

To help you make the best choice, this article provides a comprehensive summary of methods, supporting information to help you quickly decide on which technique to use, and links to in-depth articles.

Counting should not be confused with summing. For more information about summing values in cells, columns, or rows, see Ways to sum values in a worksheet.

In this article


Simple counting

You can count the number of values in a range or table by using a simple formula, clicking a button, or by using a worksheet function.

Excel can also display the count of the number of selected cells on the Excel status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.). See the video demo that follows for a quick look at using the status bar. Also, see the section Displaying calculations and counts on the status bar for more information. You can refer to the values shown on the status bar when you want a quick glance at your data and don't have time to enter formulas.

Video: Count cells by using the Excel status bar

Watch the following video to learn how to view counts on the status bar.


Top of Page Top of Page

Count cells in a column or row by using a built-in command

Use the Count Numbers feature of the AutoSum command by selecting a range of cells that contains at least one numeric value and then, on the Home tab, in the Editing group, click the arrow next to AutoSum. Then click Count Numbers. Excel returns the count of the numeric values in the range in a cell adjacent to the range you selected. Generally, this result is displayed in a cell to the right for a horizontal range or in a cell below for a vertical range.

Using the Count Numbers AutoSum command

Top of Page Top of Page

Count cells in a range by using the COUNT function

Use the COUNT function in a formula to count the number of numeric values in a range. In the following example, the range A2:A5 contains three numbers (5, 32, and 10) and one text value ("hello"). You use the COUNT function in a formula like this: =COUNT(A2:A5). The result is 3, the number of numeric values found in the range.

 
1
2
3
4
5
6
A
Sample value
5
32
hello
10
=COUNT(A2:A5)

For more information, see the section "Count cells that contain numbers that are not in a contiguous row or column" in the article COUNT function.

Top of Page Top of Page

Count cells in a column by using the Subtotal command in an outline

Use the Subtotal command (Data tab, Outline group) to group and summarize a column of data.

If your data is in a list and you can logically group it by column values, you can create an outline to group and summarize the data.

Subtotal command groups data into an outline

As this figure shows, the sales totals are grouped by region, and it's easy to see that there are four quarterly values each for East and West. As a bonus, the values are totaled for each region, and a grand total is also calculated.

For more information, see the following articles:

Top of Page Top of Page

Count cells in a column or row by using a PivotTable

Create a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) that summarizes your data and helps you perform analysis by letting you choose the categories on which you want to view your data.

You can quickly create a PivotTable by selecting a cell in a range of data or Excel table and then, on the Insert tab, in the Tables group, clicking PivotTable.

To show the power of a PivotTable, notice that in the following example, the sales data contains many rows (there are actually 40 rows of data, but the graphic shows only a portion of those rows). The data isn't summarized, and it has no subtotals or grand total.

Data used in a PivotTable report

A PivotTable report based on the same data shows subtotals, grand totals, and provides a concise summary at a glance.

Data summarized and totaled in a PivotTable report

Creating and working with PivotTables may require some initial preparation of your data and a familiarity with some concepts.

For detailed information to help you get started, see the following articles:

Top of Page Top of Page

Count cells in a list or Excel table column by using the SUBTOTAL function

You can use the SUBTOTAL function in a formula to count the number of values in an Excel table or range of cells.

You can use the SUBTOTAL function to control whether or not hidden rows are included in the results. The function always ignores rows that have been filtered.

For example, using the function on the seven values in the following table (cells A2 through A8) returns a count of 7.

 
1
2
3
4
5
6
7
8
9
A
Units sold
25
8
12
32
11
40
16
=SUBTOTAL(2,A2:A8)

In the formula, the "2" portion specifies that the function should use the SUM function to return the count of the values in the range A2:A8 and that any hidden rows should be included. The count (the result in cell A9) is 7.

If you hid rows 4, 5, and 6 and didn't want those rows counted, you would use the SUBTOTAL function somewhat differently. Instead of specifying "2" in the formula, you specify "102," which tells Excel to ignore hidden rows. Your worksheet might look like the following (with rows 4, 5, and 6 hidden):

 
1
2
3
7
8
9
A
Units sold
25
8
40
16
=SUBTOTAL(102,A2:A8)

In this case, the function returns 4, the number of cells in the column that are not hidden and that contain values.

For more information, see the articles SUBTOTAL function.

Top of Page Top of Page

Counting based on one or more conditions

You can count the number of cells in a range that meet conditions (also known as criteria) that you specify by using a number of worksheet functions.

Video: Use the COUNT, COUNTIF, and COUNTA functions

Watch the following video to see how to use the COUNT function and how to use the COUNTIF and COUNTA functions to count only the cells that meet conditions you specify.


Top of Page Top of Page

Count cells in a range based on a single condition by using the COUNTIF function

Use the COUNTIF function to count the number of cells that meet one condition (also known as a criterion). In the following example, the function finds the count of values in the range A2:A8 that are greater than 20, which is 3. Note that the condition, ">20," must be enclosed in quotation marks.

 
1
2
3
4
5
6
7
8
9
A
Units sold
25
8
12
32
11
40
16
=COUNTIF(A2:A8,">20")

For more information, see the article COUNTIF function.

Top of Page Top of Page

Count cells in a column based on single or multiple conditions by using the DCOUNT function

To match conditions that you specify, use the DCOUNT database function.

Use the DCOUNT function when you have a column list and you find it easier to define your conditions in a separate range of cells instead of using a nested function.

In the following example, suppose you want to find the count of the months including or later than March, 2008 that had more than 400 units sold. Looking at this table, you can see that two months satisfy this requirement: April (442) and June (405).

 Note   The process that follows may not be intuitive, but it does work. It's important that you follow the directions precisely and arrange the data in your worksheet as it is shown here. You can copy the data from the table and paste it in a worksheet at cell A1. The result should appear in cell B13.

You add an additional range of cells that are arranged in a similar manner to the sales data, which is in cells A1 through B7. The additional range of cells consists of cells A10 through B11, and contains the same column labels ("Sales in units" and "Month ending"), and a condition in the row directly under each column label (cells A11 and B11).

You then enter a formula in any blank cell (its location makes no difference, but in this example, the formula is entered in cell B13). The formula in this example uses the DCOUNT function, as follows: =DCOUNT(A1:B7,,A10:B11).

 
1
2
3
4
5
6
7
8
9
10
11
12
13
A B
Sales in units Month ending
339 1/31/2008
270 2/29/2008
314 3/31/2008
442 4/30/2008
336 5/31/2008
405 6/30/2008
 
 
Sales in units Month ending
=">400" =">=3/31/2008"
 
=DCOUNT(A1:B7,,A10:B11)

The DCOUNT function inspects the data in the range A2 through A7, applies the conditions that it finds in A11 and B11, and returns 2, the number of rows that satisfy both conditions (rows 5 and 7).

For more information, see the article DCOUNT function.

Top of Page Top of Page

Count cells in a range based on multiple conditions by using the COUNTIFS function or a combination of COUNT and IF functions

Use the COUNTIFS function or a combination of the COUNT and IF functions.

Using the COUNTIF function on a range of cells

This figure shows the COUNTIFS function being used to find cars that produce more than 250 horsepower yet average more than 25 miles per gallon on the highway. The function returns 2, the number of rows that meet both conditions (rows 3 and 4).

 Note   In the COUNTIFS function, any conditions must be enclosed in quotation marks ("") — for example, "<250", ">25", or even "240".

You can count the number of times a single value appears in a range by using the COUNTIF function. For example, to see how many times the value 70 appears in the range A2 through A40, you use the formula =COUNTIF(A2:A40,70).

For more information, see the articles Count how often a value occurs and COUNTIFS function.

Top of Page Top of Page

Counting when your data contains blank values

You can count cells that either contain data or are blank by using worksheet functions.

Count nonblank cells in a range by using the COUNTA function

Use the COUNTA function to count only cells in a range that contain values.

When you count cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you. For example, you want to count all salespeople who made at least one sale in a region.

In the following example, using the function on the values in the West region sales column returns a count of 3.

 
1
2
3
4
5
6
7
8
A B
Salesperson West region sales
Andrews 24000
Atlas
Chai
Gabrielle 31000
Hansen
Zeng 8000
=COUNTA(B2:B7)

Because cells B3, B4, and B6 are blank, the COUNTA function ignores them. Only the cells that contain the values 24000, 31000, and 8000 are counted. The count (the result in cell B8) is 3.

For more information, see the articles Count nonblank cells and COUNTA function.

Top of Page Top of Page

Count nonblank cells in a list by using the DCOUNTA function

Use the DCOUNTA function to count nonblank cells in a column of records in a list or database that match conditions that you specify.

The following example uses the DCOUNTA function to count the number of records in the database that is contained in the range A4:B9 that meet the conditions specified in the criteria range A1:B2. Those conditions are that the Product ID value must be greater than or equal to 4000 and the Ratings value must be greater than or equal to 50. Just one record, in row 7, satisfies both conditions.

 
1
2
3
4
5
6
7
8
9
A B
Product ID Ratings
=">=4000" =">=50"
 
Product ID Ratings
2048 61
16384
35336 83
1024 113
512 47
=DCOUNTA(A4:B9,"Ratings",A1:B2)

For more information, see the article DCOUNTA function.

Top of Page Top of Page

Count blank cells in a contiguous range by using the COUNTBLANK function

Use the COUNTBLANK function to return the number of blank cells in a contiguous range (cells are contiguous if they are all connected in an unbroken sequence). If a cell contains a formula that returns empty text (""), that cell is counted.

 Note   When you count cells, there may be times when you want to include blank cells because they are meaningful to you. For example, you want to count all salespeople in a region, whether or not they made a sale.

For more information, see the article COUNTBLANK function.

Top of Page Top of Page

Count blank cells in a non-contiguous range by using a combination of SUM and IF functions

Use a combination of the SUM function and the IF function. In general, you do this by using the IF function in an array formula (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.) to determine whether each referenced cell contains a value, and then summing the number of FALSE values returned by the formula.

See the Knowledge Base article XL: When to Use SUM(IF()) instead of CountBlank() for additional information.

Top of Page Top of Page

Counting unique occurrences of values

You can count unique values in a range by using a PivotTable report, the COUNTIF function, a combination of functions, or by using the Advanced Filter dialog box.

Count unique values in a range by using a PivotTable report

You can use a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) to display totals and count the occurrences of unique values.

For more information, see the section "Count how often multiple values occur by using a PivotTable report" in the article Count how often a value occurs.

Top of Page Top of Page

Count unique values in a range based on a single condition by using the COUNTIF function

Use the COUNTIF function to count the number of times a value occurs in a range.

In the following example, the COUNTIF function returns 2, the number of times that the value 250 is found in the range A2:A7.

 
1
2
3
4
5
6
7
8
A
Units sold
245
 
250
 
250
N/A
=COUNTIF(A2:A7,250)

For more information, see the section "Count how often a single value occurs in a range" in the article Count how often a value occurs.

Top of Page Top of Page

Count unique values in a range based on multiple conditions by using a combination of SUM and IF functions in an array formula

Use the SUM function with the IF function. In general, you do this by using the IF function in an array formula (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.) to determine whether criteria that is composed of multiple conditions is met, and then summing the number of TRUE values returned by the formula.

In the following example, the IF function is used to examine each cell in the range A2:A10 and determine if it contains either Andrews or Chai. The number of times the TRUE value is returned is totaled by using the SUM function, resulting in 7. You can copy and paste this example into a worksheet at cell A1. After you paste the example, you'll notice that cell A11 contains a #VALUE! error. To make the formula work, you must convert it to an array formula by pressing F2 and then pressing CTRL+SHIFT+ENTER. The number 7 then appears in cell A11.

 
1
2
3
4
5
6
7
8
9
10
A B
Salesperson Invoice
Andrews 15000
Chai 11000
Andrews 11000
Chai 4000
Hansen 8000
Chai 6000
Hansen 14000
Andrews 7000
12000
Formula Description (result)
=SUM(IF((A2:A10="Andrews")+(A2:A10="Chai"),1,0)) Number of invoices for Andrews or Chai (7)

For more information, see the section "Count how often multiple text or number values occur by using functions" in the article Count how often a value occurs.

See the following Knowledge Base articles for additional tips:

Top of Page Top of Page

Count the number of unique values in a list column by using Advanced Filter

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

 Notes 

  • If you filter your data in place, values are not deleted from your worksheet — one or more rows might be hidden. Click Clear in the Sort & Filter group on the Data tab to display those values again.
  • If you only want to see the number of unique values at a quick glance, select the data after you have used the Advanced Filter (either the filtered or the copied data) and then look at the status bar. The Count value on the status bar should equal the number of unique values.

You can find the unique values by using the Advanced command (Data tab, Sort & Filter group).

The following figure shows how you use the Advanced Filter to copy only the unique records to a new location on the worksheet.

Applying the Advanced Filter to a range of data

In the following figure, column C contains the five unique values that were copied from the range in column A.

Unique values from range in column A

For more information, see the section "Count the number of unique values by using a filter" in the article Count unique values among duplicates.

Top of Page Top of Page

Count the number of unique values in a range that meet one or more conditions by using a compound formula

Use various combinations of the IF, SUM, FREQUENCY, MATCH, and LEN functions.

For more information, see the section "Count the number of unique values by using functions" in the article Count unique values among duplicates.

Also, see the article XL: How to determine the number of unique items in a list.

Top of Page Top of Page

Special cases (count all cells, count words)

You can count the number of cells or the number of words in a range by using various combinations of worksheet functions.

Count the total number of cells in a range by using ROWS and COLUMNS functions

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

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

ShowHow to copy an example

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

 Note   Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help
  • Press CTRL+C.
  • In the worksheet, select cell A1, and press CTRL+V.
  • 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.
 
1
2
3
4
5
6
A B C
Region Month Sales
East Jan $18,000
East Feb $23,000
East Mar $19,000
Formula Description (result)
=ROWS(A2:C4) * COLUMNS(A2:C4) The total number of cells in the range (9)

Top of Page Top of Page

Count words in a range by using a compound formula

Use a combination of the SUM, IF, LEN, , and SUBSTITUTE functions in an array formula (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.). The following example shows the result of using a compound formula to find the number of words in a range of 7 cells (3 of which are empty). Some of the cells contain leading or trailing spaces — the TRIM and SUBSTITUTE functions remove these extra spaces before any counting occurs.

In the following table, copy the text from A2 through A11. Before you paste the text into cell A1 on your worksheet, change the width of column A so that it is about 100.

 
1
2
3
4
5
6
7
8
9
10
11
A
Text string
Six words and four trailing spaces    
 
   Six words and three leading spaces
 
Eight words, some commas, and two trailing spaces  
 
Some characters are ignored, such as commas or periods.
 
Formula
=SUM(IF(LEN(TRIM(A2:A8))=0,0,LEN(TRIM(A2:A8))-LEN(SUBSTITUTE(A2:A8," ",""))+1))

After you have pasted the text into the worksheet at cell location A1, select cell A11, press F2, and then press SHIFT+CTRL+ENTER to enter the formula as an array formula. The correct result, 29, should appear in cell A11.

Top of Page Top of Page

Displaying calculations and counts on the status bar

When one or more cells are selected, information about the data in those cells is displayed on the Excel status bar. For example, if four cells on your worksheet are selected, and they contain the values 2, 3, a text string (such as "cloud"), and 4, all of the following values can be displayed on the status bar at the same time: Average, Count, Numerical Count, Min, Max, and Sum. Right-click the status bar to show or hide any or all of these values. These values are shown in the illustration that follows.

Status bar showing calculations and counts of selected cells

 Note   In earlier versions of Excel, these same values can be displayed on the status bar, but only one value at a time.

Top of Page Top of Page

 
 
Applies to:
Excel 2010, Excel 2007