Search Contextures Sites ![]()
Excel Count Functions -- Count Excel Cells
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)![]()
=COUNTA(A1:A5)
Count Blank Cells -- COUNTBLANKThe 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)![]()
=COUNTBLANK(A1:A5)
Count cells that match criteria -- COUNTIF
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 Excel COUNTIF Match Text Function video
Video: Use COUNTIF With an Operator
In this video, you'll see the steps for using the Excel COUNTIF function to count the number of items in a list that are over or under a specific amount.
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.
Video: COUNTIFS
This video shows how to use the COUNTIFS function to count cells based on multiple criteria.
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.
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.
=SUBTOTAL(3,D2:D10)
Count Visible Items in a Filtered ListLaurent 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.txtIncorporating 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))
Excel Function Tutorials
Excel Sum Functions
Excel VLOOKUP Function
Excel INDEX / MATCH Functions
Excel Count Functions
Excel INDIRECT FunctionExcel Function Video Tutorials:
Excel MATCH Function
Excel VLOOKUP Function
Excel Count Functions
Contextures Inc., Copyright ©2012
All rights reserved.
Search Contextures Sites ![]()
Download zipped sample Excel COUNT workbook
Last updated: January 28, 2012 5:58 PM