Home > Formulas > Statistical > Count

7 Ways to Count in Excel - Examples

Debra Dalgleish - Contextures

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.

Count Specific Items - COUNTIF

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

Count Numbers - COUNT

1.1) COUNT Formula

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.

Count Numbers - COUNT

1.2) COUNT Function Notes

Syntax

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

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

Arguments

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

Count All Data - COUNTA

2.1) COUNTA Formula

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.

2.2) COUNTA Function Notes

Syntax

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

Count Empty Cells - COUNTBLANK

3.1) COUNTBLANK Formula

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.

3.2) COUNTBLANK Function Notes

Syntax

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

Count Specific Items - COUNTIF

4.1) COUNTIF Formula

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.

4.2) COUNTIF Notes

Syntax

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

Count partial matches - COUNTIF and wildcard

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

5.1) Partial Match Formula

The following COUNTIF formula is entered in cell D2, and it refers to the criterion cell, D1:

  •  =COUNTIF(A2:A6, 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 - COUNTIFS

The COUNTIFS function will count cells that match one or more specific criteria. For example, count rows based on the following 2 criteria:

  1. contain an exact match for the text string, "Pen"
  2. have a quantity greater than or equal to 6.

Count based on multiple criteria - COUNTIFS

6.1) COUNTIFS Formula

The following COUNTIFS formula is entered in cell D3, and it refers to the two criteria cells, D1 (Pen) and D2 (>=6):

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

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

Count based on multiple criteria - COUNTIFS

6.2) COUNTIFS Notes

Syntax

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

The SUBTOTAL function will ignore hidden items, and only count the visible items.

Count in filtered list - SUBTOTAL

7.1) SUBTOTAL Formula

In the screen shot shown above, there is a list on the worksheet, from cell A1 to B6. In column A, a filter has been applied, 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)

In this example, the formula result is 3, because there are 3 visible numbers in column B, in the filtered list.

7.2) SUBTOTAL Notes

Syntax

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

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

  • 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.
  • function numbers for subtotal formula

b) Function Numbers 101 to 111

  • rows that were hidden by filtering are ignored
  • also, manually hidden rows are ignored
  • function numbers for subtotal formula 101 to 111

Get the Sample 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.

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

 

 

Count Specific Items

 

 

Last updated: May 22, 2024 6:44 PM