Ways to count values in a worksheet

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.

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. 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.

Demo: Count cells by using the Excel status bar

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

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.

Count cells in a range by using a 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.

Count cells in a column by using 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.

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.

Count cells in a column or row by using a PivotTable

Create a PivotTable report 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.

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

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:

Count cells in a list or Excel table column by using a 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.

Count cells in a range based on a single condition

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.

Count cells in a column based on single or multiple conditions

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).

Count cells in a range based on multiple conditions by using a single function or a combination of functions

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

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).

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 a 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.

Count nonblank cells in a list by using a 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.

Count blank cells in a contiguous range by using a 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.

Count blank cells in a non-contiguous range by using a combination of 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 to determine whether each referenced cell contains a value, and then summing the number of FALSE values returned by the formula.

Count unique values in a range based on multiple conditions by using a combination of 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 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.

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.

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

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 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.

How 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

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.

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. 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.

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.

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.

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