Excel Count Functions -- Count Excel Cells

Contextures - Microsoft Excel Tips and Tutorials
Search Contextures Sites

Home
Excel Tips
Excel Files
Products
Blog
Contact
RSS

Contextures
Excel news
by email

Email:

Name (optional):

We respect your email privacy

Excel Count Functions

Examples and video tutorials show how to count Excel cells with numbers, text, blanks, or cells that contain specific words or other criteria.

Video: Count Cells in Excel
Count Cells with Numbers -- COUNT Function
Count Cells with Data-- COUNTA
Count Blank Cells -- COUNTBLANK

Count cells that match criteria -- COUNTIF
    - Match criterion exactly
    - Video: Count Cells that Match Text
    - Match criterion in a string
    - Match criterion using operator
    - Video: Use COUNTIF With an Operator
    - Match criteria in a range
    - Video: Change Operator for COUNTIF Function
    - Change Operator for COUNTIF Function

Video: COUNTIFS
Count cells that match multiple criteria -- COUNTIFS

Count cells that match multiple criteria -- SUMPRODUCT
Count the Numbers in a Filtered List -- SUBTOTAL
Count Specific Items in a Filtered List
Count Visible Items in a Filtered List

Download the COUNT Sample File

Video: Count Cells in Excel

This video shows the COUNT function, the COUNTA function, and the COUNTBLANK function, and warns of the quirks in counting some types of cells.

Written instructions are below the video.

Count Cells with Numbers -- Excel COUNT

The COUNT function will count cells that contain numbers. Its syntax is:
    =COUNT(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the COUNT formula.

The following COUNT function example uses one argument -- a reference to cells A1:A5.

Enter the sample data on your worksheet

In cell A7, enter an COUNT formula, to count the numbers in column A:   =COUNT(A1:A5)

Press the Enter key, to complete the formula.

The result will be 3, the number of cells that contain numbers.
Cell A1 isn't counted, because it contains text.

Note: Since dates are stored as numbers, the COUNT function will include any cells that contain dates.

Count Cells with Data -- COUNTA

The COUNTA function will count cells that are not empty. Its syntax is:
    =COUNTA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the formula. The following example uses one argument -- a reference to cells A1:A5.

Enter the sample data on your worksheet

In cell A7, enter a COUNTA formula, to count the numbers in column A:   =COUNTA(A1:A5)

Press the Enter key, to complete the formula.

The result will be 4, the number of cells that contain data.

Note: COUNTA will count cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2). It will also count cells which had formulas that evaluated to "", but then were converted to values (Edit | Paste Special, Values). You can see apostrophes in those "non-blank" converted cells, if you add a check mark to Tools | Options, Transition tab, "Transition navigation keys."

View the steps in a short Excel Count Functions video
(COUNT, COUNTA, COUNTBLANK)

Count Blank Cells -- COUNTBLANK

The COUNTBLANK function will count cells that are empty. Its syntax is:
    =COUNTBLANK(range).
The following example uses a reference to cells A1:A5.

Enter the sample data on your worksheet

In cell A7, enter a COUNTBLANK formula, to count the numbers in column A:   =COUNTBLANK(A1:A5)

Press the Enter key, to complete the formula.

The result will be 1, the number of empty cells.

Note: COUNTBLANK will count cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2). It will also count cells which had formulas that evaluated to "", but then were converted to values (Edit | Paste Special, Values). You can see apostrophes in those "non-blank" converted cells, if you add a check mark to Tools | Options, Transition tab, "Transition navigation keys."

View the steps in a short Excel Count Functions video
(COUNT, COUNTA, COUNTBLANK)

Count cells that match criteria -- COUNTIF

Match criterion exactly

In Excel, count cells that meet a specific criterion. In this example only the Pen orders will be counted.

Select the cell in which you want to see the count (cell A12 in this example)

Type an equal sign (=) to start the formula

Type:   COUNTIF(

Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked

Type a comma, to separate the arguments

Type the criterion. In this example, you're checking for text, so type the word in double quotes:   "Pen"
Note: upper and lower case are treated equally

Type a closing bracket
The completed formula is: =COUNTIF(A1:A10,"Pen")

Press the Enter key to complete the entry

The result will be 4, the number of cells that contain "Pen"

Video: Count Cells that Match Text

This video shows how to use the COUNTIF function to count cells that contain a specific string of text, such as "Pen".

How many orders were placed for any kind of pen, such as "Gel Pen", "Pen" or even a "Pencil"?

Match criterion in a string

In Excel, count cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be counted, because they contain the string "pen".

Select the cell in which you want to see the count (cell A12 in this example)

Type an equal sign (=) to start the formula

Type:   COUNTIF(

Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked

Type a comma, to separate the arguments

Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters:   "*Pen*"
Note: upper and lower case are treated equally

Type a closing bracket
The completed formula is: =COUNTIF(A1:A10,"*Pen*")

Press the Enter key to complete the entry

The result will be 6, the number of cells that contain the string, "Pen"

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 7 above could be changed to:
    =COUNTIF(A1:A10,"*" & B12 & "*")
if cell B12 contained the text — pen.

View the steps in a short COUNTIF Match Text Function video

Criterion and operator

You can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be counted.

Select the cell in which you want to see the count (cell A12 in this example)

Type an equal sign (=) to start the formula

Type:   COUNTIF(

Select the cells that contain the values to check for the criterion. In this example, cells B1:B10 will be checked

Type a comma, to separate the arguments

Type the criterion. In this example, you're checking for rows where the quantity is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes: ">=10"
Note: Even though this is a numerical criterion, it must enclosed in double quote marks.

Type a closing bracket

The completed formula is:
          =COUNTIF(B1:B10,">=10")

Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 8 above could be changed to:
    =COUNTIF(B1:B10,">=" & B12)
if cell B12 contained the number — 10.

Or, you could use a function as part of the criterion. For example:
   =COUNTIF(A1:A10,"<"&TODAY())

Video: Use COUNTIF With an Operator

In this video, you'll see the steps for using the COUNTIF function to count the number of items in a list that are over or under a specific amount.

Match criteria in a range

You can combine COUNTIF formulas, to count rows that are within a range of values. In this example, the formula will count rows where the quantity is between 5 and 10 (inclusive).

Select the cell in which you want to see the count (cell A12 in this example)

Type a formula to count rows greater than or equal to 5:
          =COUNTIF(B1:B10,">=5")

Type a minus sign

Type a formula to count rows greater than 10:
          COUNTIF(B1:B10,">10")

The completed formula is:
=COUNTIF(B1:B10,">=5")-COUNTIF(B1:B10,">10")

Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 8 above could be changed to:
    =COUNTIF(B1:B10,">=" & B12) -
            COUNTIF(B1:B10,">" & C12)
if cell B12 contained the number — 5 and cell C12 contained the number — 10.

Video: Change Operator for COUNTIF Function

Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop down list.

Watch this video to see the steps. The written instructions are below the video.

Change Operator for COUNTIF Function

Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop down list. Then, refer to that operator in the formula.

Create a Drop Down List of Operators

To create a drop down list operators:

On a different sheet in the workbook, type a list of operators in a column.

Select the cells in the list, and name that list as OpList.

On the main sheet, select the cell where you want the drop down list.

Use the data validation command to create the drop down list, based on the named range -- OpList

Use the Drop Down List

Change your COUNTIF formula, to replace the typed operator with a reference to the cell with the drop down list.

Select one of the operators, from the drop down list, and the formula result will change.

Video: COUNTIFS

This video shows how to use the COUNTIFS function to count cells based on multiple criteria.

Count cells that match multiple criteria -- COUNTIFS

Match multiple criteria

In Exce 2007 and later versions, you can use the COUNTIFS function to count rows that meet two or more criteria. In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be counted.

Select the cell in which you want to see the total

Type an equal sign (=) to start the formula

Type:   COUNTIFS(

Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked

Type a comma, and the first criterion:   "Pen"
Note: Because this is a text criterion, it is enclosed in double quote marks.

To start the next set of criteria, type a comma

Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked

Type a comma, and the second criterion: ">=10"
Note: Because this criterion includes operators, it is enclosed in double quote marks. To count rows where the quantity is equal to 10, only the number 10 would be required.

Finish with a closing bracket: )

The completed formula is shown below.

Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below. If using operators, enclose them in double quote marks.

Use typed criteria:

=COUNTIFS(A2:A10,"Pen",B2:B10,">=10")

or cell references:

=COUNTIFS(A2:A10,D3,B2:B10,">=" & E3)

Count cells that match multiple criteria -- SUMPRODUCT

Match multiple criteria

In Excel, count rows that meet two or more criteria. In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be counted.

Select the cell in which you want to see the total

Type an equal sign (=) to start the formula

Type:   SUMPRODUCT(--(

Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked

Type the first criterion:   ="Pen"
Note: Because this is a text criterion, it is enclosed in double quote marks.

Type ),--(

Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked

Type the second criterion:   >=10
Note: Because this is a numerical criterion, it isn't enclosed in double quote marks.

Finish with closing brackets: ))

The completed formula is shown below.

Press the Enter key to complete the entry

Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below

Use typed criteria:

=SUMPRODUCT(--(A2:A10="Pen"),--(B2:B10>=10))

or cell references:

=SUMPRODUCT(--(A2:A10=D2),--(B2:B10>=E2))

Count Rows in a Filtered List -- SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows.

Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics

Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.

Select the cell immediately below the column you want to sum.

Click the AutoSum button on the Excel's Standard toolbar.

If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button.

A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column

The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. The default is 9, which tells Excel to SUM the numbers.

Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. Look in Excel's Help for a complete list.

To Count all the non-empty cells in column D, use a 3 as the first argument:
=SUBTOTAL(3,D2:D10)

Press the Enter key to complete the formula entry.

Note: In Excel 2003, you can use the formula:
    =SUBTOTAL(103,D2:D10)
to subtotal rows which have been manually hidden, or filtered.

Count Visible Items in a Filtered List

Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site:
            http://j-walk.com/ss/excel/eee/eee001.txt

Incorporating that technique, SUMPRODUCT can be used to count visible items in a filtered table. In the following example, column D has been filtered for amounts greater than 100. The following formula will count the number of visible rows that contain "Pen" in column A.

From the dropdown list in cell D1, select Custom.

Filter for rows greater than 100.

In cell A12, type: Pen

In cell B12, enter the following formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)
              -MIN(ROW(A1:A10)),,1)), --(A1:A10=A12))

Press the Enter key to complete the formula entry.

Download the COUNT Sample File

Download zipped Count Functions sample workbook

Excel Function Tutorials

Sum Functions
VLOOKUP Function
INDEX / MATCH Functions
Count Functions
INDIRECT Function

Excel Function Video Tutorials:

MATCH Function
VLOOKUP F unction
Count Functions

Home

Excel Tips

Excel Files

Products

Blog

Contact

Privacy Policy

Contextures Inc., Copyright �2013
All rights reserved.

Debra Dalgleish

Search Contextures Sites

Download zipped sample Excel COUNT workbook

Last updated: May 21, 2013 10:59 AM

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Count Functions Examples and video tutorials show how to count Excel cells with numbers, text, blanks, or cells that contain specific words or other criteria. Video: Count Cells in Excel Count Cells with Numbers -- COUNT Function Count Cells with Data-- COUNTA Count Blank Cells -- COUNTBLANK Count cells that match criteria -- COUNTIF - Match criterion exactly - Video: Count Cells that Match Text - Match criterion in a string - Match criterion using operator - Video: Use COUNTIF With an Operator - Match criteria in a range - Video: Change Operator for COUNTIF Function - Change Operator for COUNTIF Function Video: COUNTIFS Count cells that match multiple criteria -- COUNTIFS Count cells that match multiple criteria -- SUMPRODUCT Count the Numbers in a Filtered List -- SUBTOTAL Count Specific Items in a Filtered List Count Visible Items in a Filtered List Download the COUNT Sample File Video: Count Cells in Excel This video shows the COUNT function, the COUNTA function, and the COUNTBLANK function, and warns of the quirks in counting some types of cells. Written instructions are below the video. Count Cells with Numbers -- Excel COUNT The COUNT function will count cells that contain numbers. Its syntax is: =COUNT(value1, value2,...value30). The arguments (e.g. value1) can be cell references, or values typed into the COUNT formula. The following COUNT function example uses one argument -- a reference to cells A1:A5. Enter the sample data on your worksheet In cell A7, enter an COUNT formula, to count the numbers in column A: =COUNT(A1:A5) Press the Enter key, to complete the formula. The result will be 3, the number of cells that contain numbers. Cell A1 isn't counted, because it contains text. Note: Since dates are stored as numbers, the COUNT function will include any cells that contain dates. Count Cells with Data -- COUNTA The COUNTA function will count cells that are not empty. Its syntax is: =COUNTA(value1, value2,...value30). The arguments (e.g. value1) can be cell references, or values typed into the formula. The following example uses one argument -- a reference to cells A1:A5. Enter the sample data on your worksheet In cell A7, enter a COUNTA formula, to count the numbers in column A: =COUNTA(A1:A5) Press the Enter key, to complete the formula. The result will be 4, the number of cells that contain data. Note: COUNTA will count cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2). It will also count cells which had formulas that evaluated to "", but then were converted to values (Edit \| Paste Special, Values). You can see apostrophes in those "non-blank" converted cells, if you add a check mark to Tools \| Options, Transition tab, "Transition navigation keys." View the steps in a short Excel Count Functions video (COUNT, COUNTA, COUNTBLANK) Count Blank Cells -- COUNTBLANK The COUNTBLANK function will count cells that are empty. Its syntax is: =COUNTBLANK(range). The following example uses a reference to cells A1:A5. Enter the sample data on your worksheet In cell A7, enter a COUNTBLANK formula, to count the numbers in column A: =COUNTBLANK(A1:A5) Press the Enter key, to complete the formula. The result will be 1, the number of empty cells. Note: COUNTBLANK will count cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2). It will also count cells which had formulas that evaluated to "", but then were converted to values (Edit \| Paste Special, Values). You can see apostrophes in those "non-blank" converted cells, if you add a check mark to Tools \| Options, Transition tab, "Transition navigation keys." View the steps in a short Excel Count Functions video (COUNT, COUNTA, COUNTBLANK) Count cells that match criteria -- COUNTIF Match criterion exactly In Excel, count cells that meet a specific criterion. In this example only the Pen orders will be counted. Select the cell in which you want to see the count (cell A12 in this example) Type an equal sign (=) to start the formula Type: COUNTIF( Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked Type a comma, to separate the arguments Type the criterion. In this example, you're checking for text, so type the word in double quotes: "Pen" Note: upper and lower case are treated equally Type a closing bracket The completed formula is: =COUNTIF(A1:A10,"Pen") Press the Enter key to complete the entry The result will be 4, the number of cells that contain "Pen" Video: Count Cells that Match Text This video shows how to use the COUNTIF function to count cells that contain a specific string of text, such as "Pen". How many orders were placed for any kind of pen, such as "Gel Pen", "Pen" or even a "Pencil"? Match criterion in a string In Excel, count cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be counted, because they contain the string "pen". Select the cell in which you want to see the count (cell A12 in this example) Type an equal sign (=) to start the formula Type: COUNTIF( Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked Type a comma, to separate the arguments Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk () wildcard characters: "Pen" Note: upper and lower case are treated equally* Type a closing bracket The completed formula is: *=COUNTIF(A1:A10,"Pen")* Press the Enter key to complete the entry The result will be 6, the number of cells that contain the string, "Pen" Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 7 above could be changed to: *=COUNTIF(A1:A10,"" & B12 & "")* if cell B12 contained the text — pen. View the steps in a short COUNTIF Match Text Function video Criterion and operator You can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be counted. Select the cell in which you want to see the count (cell A12 in this example) Type an equal sign (=) to start the formula Type: COUNTIF( Select the cells that contain the values to check for the criterion. In this example, cells B1:B10 will be checked Type a comma, to separate the arguments Type the criterion. In this example, you're checking for rows where the quantity is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes: ">=10" Note: Even though this is a numerical criterion, it must enclosed in double quote marks. Type a closing bracket The completed formula is: =COUNTIF(B1:B10,">=10") Press the Enter key to complete the entry Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 8 above could be changed to: =COUNTIF(B1:B10,">=" & B12) if cell B12 contained the number — 10. Or, you could use a function as part of the criterion. For example: =COUNTIF(A1:A10,"<"&TODAY()) Video: Use COUNTIF With an Operator In this video, you'll see the steps for using the COUNTIF function to count the number of items in a list that are over or under a specific amount. Match criteria in a range You can combine COUNTIF formulas, to count rows that are within a range of values. In this example, the formula will count rows where the quantity is between 5 and 10 (inclusive). Select the cell in which you want to see the count (cell A12 in this example) Type a formula to count rows greater than or equal to 5: =COUNTIF(B1:B10,">=5") Type a minus sign Type a formula to count rows greater than 10: COUNTIF(B1:B10,">10") The completed formula is: =COUNTIF(B1:B10,">=5")-COUNTIF(B1:B10,">10") Press the Enter key to complete the entry Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 8 above could be changed to: =COUNTIF(B1:B10,">=" & B12) - COUNTIF(B1:B10,">" & C12) if cell B12 contained the number — 5 and cell C12 contained the number — 10. Video: Change Operator for COUNTIF Function Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop down list. Watch this video to see the steps. The written instructions are below the video. Change Operator for COUNTIF Function Instead of typing the operator into the COUNTIF formula, as shown above, you can create a list of all possible operators, and select one from a drop down list. Then, refer to that operator in the formula. Create a Drop Down List of Operators To create a drop down list operators: On a different sheet in the workbook, type a list of operators in a column. Select the cells in the list, and name that list as OpList. On the main sheet, select the cell where you want the drop down list. Use the data validation command to create the drop down list, based on the named range -- OpList Use the Drop Down List Change your COUNTIF formula, to replace the typed operator with a reference to the cell with the drop down list. Select one of the operators, from the drop down list, and the formula result will change. Video: COUNTIFS This video shows how to use the COUNTIFS function to count cells based on multiple criteria. Count cells that match multiple criteria -- COUNTIFS Match multiple criteria In Exce 2007 and later versions, you can use the COUNTIFS function to count rows that meet two or more criteria. In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be counted. Select the cell in which you want to see the total Type an equal sign (=) to start the formula Type: COUNTIFS( Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked Type a comma, and the first criterion: "Pen" Note: Because this is a text criterion, it is enclosed in double quote marks. To start the next set of criteria, type a comma Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked Type a comma, and the second criterion: ">=10" Note: Because this criterion includes operators, it is enclosed in double quote marks. To count rows where the quantity is equal to 10, only the number 10 would be required. Finish with a closing bracket: ) The completed formula is shown below. Press the Enter key to complete the entry Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below. If using operators, enclose them in double quote marks. Use typed criteria: =COUNTIFS(A2:A10,"Pen",B2:B10,">=10") or cell references: =COUNTIFS(A2:A10,D3,B2:B10,">=" & E3) Count cells that match multiple criteria -- SUMPRODUCT Match multiple criteria In Excel, count rows that meet two or more criteria. In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be counted. Select the cell in which you want to see the total Type an equal sign (=) to start the formula Type: SUMPRODUCT(--( Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked Type the first criterion: ="Pen" Note: Because this is a text criterion, it is enclosed in double quote marks. Type ),--( Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked Type the second criterion: >=10 Note: Because this is a numerical criterion, it isn't enclosed in double quote marks. Finish with closing brackets: )) The completed formula is shown below. Press the Enter key to complete the entry Note: Instead of typing the criterion in a formula, you can refer to a cell, as shown in the second formula below Use typed criteria: =SUMPRODUCT(--(A2:A10="Pen"),--(B2:B10>=10)) or cell references: =SUMPRODUCT(--(A2:A10=D2),--(B2:B10>=E2)) Count Rows in a Filtered List -- SUBTOTAL After you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders. Select the cell immediately below the column you want to sum. Click the AutoSum button on the Excel's Standard toolbar. If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button. A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. The default is 9, which tells Excel to SUM the numbers. Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. Look in Excel's Help for a complete list. To Count all the non-empty cells in column D, use a 3 as the first argument: =SUBTOTAL(3,D2:D10) Press the Enter key to complete the formula entry. Note: In Excel 2003, you can use the formula: =SUBTOTAL(103,D2:D10) to subtotal rows which have been manually hidden, or filtered. Count Visible Items in a Filtered List Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site: http://j-walk.com/ss/excel/eee/eee001.txt Incorporating that technique, SUMPRODUCT can be used to count visible items in a filtered table. In the following example, column D has been filtered for amounts greater than 100. The following formula will count the number of visible rows that contain "Pen" in column A. From the dropdown list in cell D1, select Custom. Filter for rows greater than 100. In cell A12, type: Pen In cell B12, enter the following formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10) -MIN(ROW(A1:A10)),,1)), --(A1:A10=A12)) Press the Enter key to complete the formula entry. Download the COUNT Sample File Download zipped Count Functions sample workbook