Home > Formulas > Statistical > Count 7 Ways to Count in Excel - Examples Excel has a COUNT function, which counts numbers only. If you need to count all types of data, use COUNTA. See my top 7 ways to count in Excel, including formulas that count cells based on specific criteria, or just the visible rows in a filtered list. And check out the "Down the Rabbit Hole" section, for extra tips!

Video: 7 Ways to Count in Excel

To see a quick overview of 7 ways to get a total count of cells in Excel, watch my 77-second video.

There are written steps for each count function example, below the video.

1) Count Numbers - COUNT

The COUNT function will count cells that contain numbers.

For example, in the list shown below, there are 2 cells with numbers -- A2 and A4 (dates are stored as numbers in Excel).

To count the numbers, I used this COUNT formula in cell C2:

•  =COUNT(A2:A5)  

The formula result is 2, because the text value and blank cell are not counted.

2) Count All Data - COUNTA

The Excel COUNTA function will count cells that are not empty.

For example, in the list shown below, there are 2 cells with numbers -- A2 and A5. Cell A3 contains text, and cell A5 is blank (no data)

To count the cells that contain any type of data, I used this COUNTA formula in cell C2:

•  =COUNTA(A2:A5)  

The formula result is 3, because the blank cell is not counted.

3) Count Empty Cells - COUNTBLANK

The COUNTBLANK function will count the following:

• cells that are empty (blank)
• cells that contain an empty string

For example, in the list shown below, there are 2 cells with numbers -- A2 and A5. Cell A3 contains text, and cell A5 is blank (no data)

To count the blank cells, I entered this formula in cell C2:

•  =COUNTBLANK(A2:A5)  

In the example shown above, the formula result is 1, because there is one completely empty cell in the range A2:A5.

4) Count Specific Items - COUNTIF

The COUNTIF function will count cells that match one specific criterion. For example, count cells that contain an exact match for the text string, "Pen".

To count the cells that match the criterion, I entered this formula in cell D2:

•   =COUNTIF(A2:A6, D1)

In the example shown above, the formula result is 2, because there are two cells that match the criterion in cell D1.

Learn More: Go to more examples for counting specific items.

5) Count Partial Match - COUNTIF & Wildcard

In Excel, you can use wildcard characters, such as an asterisk (*), or question mark (?), to count cells that are a partial match for a criterion.

• asterisk (*) - represents any number of characters in that position, including zero characters
• question mark (?) - represents one character in that position

Here's the formula in cell D2, and it refers to the criterion cell, D1:

•  =COUNTIF(A2:A6, D1)  

The formula result is 3, because there are 3 items that contain the string "pen", exactly or partially.

Learn More: Go to more examples for counting specific items.

6) Count With Multiple Criteria - COUNTIFS

The COUNTIFS function counta cells that match one or more specific criteria. In the screen shot below, the formula uses 2 criteria:

1. Item is an exact match for "Pen"
2. Quantity is greater than or equal to 6.

Here's the formula in cell D3, and it refers to the two criteria cells):

•  =COUNTIFS(A2:A6, D1, B2:B6, D2)

The formula result is 1, because there is one pen item with a quantity greater than or equal to 6.

Learn More: Go to more examples for counting with multiple criteria.

7) Count in Filtered List - SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows in the filtered data. SUBTOTAL ignores hidden items, and only counts the visible items.

In the screen shot shown above, column A is filtered, to show Pen and Pencil items only. In cell D1, I entered this formula, to count the visible cells in the Qty column:

• =SUBTOTAL(102,B2:B6)

The formula result is 3, because there are 3 visible numbers in column B.

Learn More: Go to more examples for counting in a filtered list.

Get the Excel Files ✅

1. 7 Ways to Count: Download the 7 Ways to Count sample workbook, to follow along with the 7 Ways to Count video. The zipped file is in xlsx format, and does not contain any macros.
2. More Ways to Count: Download the Count Functions sample workbook, which has many more count formula examples.. The zipped file is in xlsx format, and does not contain any macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

🐰 Down the Rabbit Hole 🐰

In this section, you'll find notes on Excel functions used in the 7 ways to count. These things are nice to know, but not essential, so you can skip this section, and get back to work. Or dive in, for some extra Excel fun and knowledge!

1) COUNT Function Notes

2) COUNTA Function Notes

3) COUNTBLANK Function Notes

4) COUNTIF Function Notes

5) COUNTIF & Wildcard Notes

6) COUNTIFS Function Notes

7) SUBTOTAL Function Notes

1) COUNT Function Notes

The COUNT function syntax has one required argument, and up to 255 additional items, that are optional

• =COUNT(value1, [value2],...])

For the COUNT function arguments, you can use any combination of valid cell references, or typed values. For example:

• =COUNT(A2, A3, A4:A5)
• =COUNT(A2, 3, 55, "XYZ")

2) COUNTA Function Notes

The COUNTA function syntax has one required argument, and up to 255 additional items, that are optional

• =COUNTA(value1, [value2],...])

What Does It Count?

COUNTA counts the following items:

• cells with any type of information in them, such as formulas, text, numbers, error values, and logical values.
• cells that contain the empty strings (""), that some formulas return.

However, COUNTA does not count blank (empty) cells

▶

Tip: If COUNTA appears to be counting blank cells, check the troubleshooting suggestions on the Fix Blank Cells page.

3) COUNTBLANK Function Notes

The COUNTBLANK function argument syntax is:
    =COUNTA(value1, [value2],...]) .
The arguments (e.g. value1) can be cell references, or values typed into the formula

4) COUNTIF Notes

In the COUNTIF function syntax, there are two required arguments:

=COUNTIF(range, criteria)

• range: (required) Where you want to look for the values
• criteria: (required) What you want to look for, such a specific number or text string

5) COUNTIF / Wildcard Notes

In Excel, you can use wildcard characters, such as an asterisk (*), or question mark (?), to count cells that contain a partial match for a criterion.

1. asterisk (*) - represents any number of characters in that position, including zero characters
2. question mark (?) - represents one character in that position
3. tilde (~) - flags characters that can be used as wildcards.

a) The * wildcard

The asterisk (*) wildcard character represents any number of characters in that position, including zero characters.

In the example shown below, cell D1 has the wildcard criteria: *n*l*

In cell D2, the COUNTIF formula returns a count of 1

•  =COUNTIF(A2:A6, D1)  

There is 1 item that contains an n, with an i, somewhere after the n.

b) The ? wildcard

The question mark (?) wildcard character represents one characters in that position.

In the example shown below, cell D1 has the wildcard criteria: ?e*

In cell D2, the COUNTIF formula returns a count of 3

•  =COUNTIF(A2:A6, D1)  

There are 3 items that have 1 character, followed by an e, then any number of characters.

c) The ~ wildcard

The tilde (~) wildcard character lets you search for characters that are used as wildcards.

In the example shown below, cell D1 has the wildcard criteria: *~**

In cell D2, the COUNTIF formula returns a count of 2

•  =COUNTIF(A2:A6, D1)  

There are 2 items that have any number of characters, followed by an asterisk (*), then any number of characters.

6) COUNTIFS Notes

In the COUNTIFS function syntax, there are two required arguments:

=COUNTIFS(criteria_range1, criteria1,...)

• criteria_range1: (required) The range where you want to look for the first criteria
• criteria1: (required) First criteria to look for, such a specific number or text string

Note: Additional pairs of criteria ranges and criteria can be included in the formula.

7) SUBTOTAL Notes

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

SUBTOTAL(function_num,ref1,[ref2],...)

• function_num: (required) number that specifies which function to use for the subtotal.
• ref1: (required) first range of cells that you want to subtotal

Function Numbers

In the example shown above, I used function number 102, which tells Excel to use the COUNT function.

There are two sets of function numbers, in the SUBTOTAL drop-down list.

a) Function Numbers 1 to 11

• only the rows that were hidden by filtering are ignored
• manually hidden rows are NOT ignored.
• 

b) Function Numbers 101 to 111

• rows that were hidden by filtering are ignored
• also, manually hidden rows are ignored
• 

• Tip: For more ways to count rows in a filtered list, go to the Excel Filtered Rows Count or Sum page.

More Ways to Count in Excel

Count If Greater Than

Filtered Rows, Count

Date Range, Sum or Count

Calculation Options

Fix Blank Cells

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

Subtotal Feature