Home > Formulas > Statistical > Count Excel Count Function ExamplesSimple examples show 7 different ways to count with Excel formulas. Count numbers only, or count all kinds of data. Count only cells that are empty, or count based on one or more specific criteria. Or, only count visible rows in a filtered list. Short videos, Excel files, written steps 
Author: Debra Dalgleish 
Video: 7 Ways to Count in ExcelTo see a quick overview of 7 ways to get a total count of cells in Excel, watch this 77second video. There are written steps for each count function example, below the video. 
1) Count Numbers  COUNTThe COUNT function will count cells that contain numbers. 
COUNT SyntaxThe COUNT function syntax has one required argument, and up to 255 additional items, that are optional

COUNT Function ExampleIn the screen shot above, there are 4 different types of data entered in column A:

COUNT FormulaThe following COUNT formula is entered in cell C2:
Excel counts the number (A2) and the date (A4), so the formula result, in cell C2, is a count of 2. 
Note: The text value and the blank cell are not counted.
2) Count All Data  COUNTAThe Excel COUNTA function will count cells that are not empty. COUNTA counts cells with any type of information in them, such as formulas, text, numbers, error values, and logical values. It even counts the empty strings (""), that some formulas return. 
COUNTA SyntaxThe COUNTA function argument syntax
is: 
COUNTA ExampleIn the screen shot above, are different types of data entered in column A:

The following COUNTA formula is entered in cell C2:
Excel counts the number (A2), the text (A3), and the date (A4), so the formula result, in cell C2, is a count of 3.
Note: The blank cell is not counted.
3) Count Empty Cells  COUNTBLANKThe COUNTBLANK function will count cells that are empty (blank), and cells that contain an empty string 
The COUNTBLANK function argument syntax
is:
=COUNTA(value1, [value2],...]) .
The arguments (e.g. value1) can be cell references, or values typed
into the formula.
COUNTBLANK ExampleIn the screen shot above, are different types of data entered in column A:

The following COUNTBLANK formula is entered in cell C2:
In this example, the formula result is 1, because there is one completely empty cell in the range.
4) Count Specific Items  COUNTIFThe COUNTIF function will count cells that match a specific criterion. For example, count cells that contain an exact match for the text string, "Pen". 
In the COUNTIF function syntax, there are two required arguments:
=COUNTIF(range, criteria)
COUNTIF ExampleIn the screen shot below, there are:

The following COUNTIF formula is entered in cell D2, and it refers to the criterion cell, D1:
In this example, the formula result is 2, because there are 2 pen items in the range.
5) Count Partial Match  COUNTIF & WildcardIn Excel, you can use wildcard characters, such as an asterisk (*), or question mark (?), to count cells that are a partial match for a criterion.

Partial Match Formula ExampleIn the screen shot below, there are:
Partial Match FormulaThe following COUNTIF formula is entered in cell D2, and it refers to the criterion cell, D1:

In this example, the formula result is 3, because there are 3 items that contain the string "pen", exactly or partially. 
6) Count With Multiple Criteria  COUNTIFSThe COUNTIFS function will count cells that match one or more specific criterion. For example, count rows that:

In the COUNTIFS function syntax, there are two required arguments:
=COUNTIFS(criteria_range1, criteria1,...)
Note: Additional pairs of criteria ranges and criteria can be included in the formula.
COUNTIFS ExampleIn the screen shot below, there are:

The following COUNTIFS formula is entered in cell D3, and it refers to the two criteria cells, D1 and D1:
In this example, the formula result is 1, because there is one pen item with a quantity greater than or equal to 6.
7) Count in Filtered List  SUBTOTALAfter you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows in the filtered data.

SUBTOTAL Syntax
In the SUBTOTAL function syntax, there are two required arguments:

There are two sets of function numbers, in the SUBTOTAL dropdown list.
If you use a function number between 1 and 11:
If you use a function number between 101 and 111:
Tip: For more details on the function numbers, go to the Excel SUBTOTAL function page.
In this example, there is a list on the worksheet, from cell A1 to D10. In column A, a filter has been applied, to show Pen and Pencil items only.
In cell D1, the following formula is entered, to count the visible quantities:
The function number is 102, for COUNT.
In this video, I show examples for the COUNT function, the COUNTA function, and the COUNTBLANK function. Also, I show a couple of problems you might run into, when counting some types of cells.
This video shows how to use the Excel COUNTIF function to count cells that contain a specific string of text, such as "Pen".
You can also find text that is part of a cell  how many orders were placed for any kind of pen, such as "Gel Pen", "Pen" or even a "Pencil"?
This video shows how to use the COUNTIFS function to count cells based on multiple criteria.
Another example in the video shows how to use the SUMPRODUCT function to count with multiple criteria.
Get the Sample Files

More Ways to Count in ExcelCount Criteria in Other Column 
Last updated: February 6, 2024 10:43 AM