Home > Formulas > Statistical > Count

Excel Count Function Examples

Simple 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

Count Specific Items - COUNTIF

Author: Debra Dalgleish

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

Count Numbers - COUNT

COUNT Syntax

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

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

COUNT Function Example

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

  • A2: number - 50
  • A3: text - N/A
  • A4: date - 30-Jan
    • Note: Dates are stored as numbers in Excel
  • A5: blank cell

COUNT Formula

The following COUNT formula is entered in cell C2:

  •  =COUNT(A2:A5)  

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.

Count Numbers - COUNT

COUNT Function Notes

  • COUNT function arguments can be cell references, or values typed into the COUNT formula.
  • Because dates are stored as numbers in Excel, the COUNT function will count any cells that contain valid dates.
  • You can refer to cell ranges that include non-numeric values, but only the cells with numbers will be counted

2) Count All Data - COUNTA

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

Count All Data - COUNTA

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 Syntax

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

COUNTA Example

In the screen shot above, are different types of data entered in column A:

  • A2: number - 50
  • A3: text - N/A
  • A4: blank cell
  • A5: number - 10

COUNTA Formula

The following COUNTA formula is entered in cell C2:

  •  =COUNTA(A2:A5)  

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.

Count All Data - COUNTA

3) Count Empty Cells - COUNTBLANK

The COUNTBLANK function will count cells that are empty (blank), and cells that contain an empty string

Count Empty Cells - COUNTBLANK

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

COUNTBLANK Example

In the screen shot above, are different types of data entered in column A:

  • A2: number - 50
  • A3: text - N/A
  • A4: blank cell
  • A5: number - 10

COUNTBLANK Formula

The following COUNTBLANK formula is entered in cell C2:

  •  =COUNTBLANK(A2:A5)  

In this example, the formula result is 1, because there is one completely empty cell in the range.

Count Empty Cells - COUNTBLANK

4) Count Specific Items - COUNTIF

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

Count Specific Items - COUNTIF

COUNTIF 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

COUNTIF Example

In the screen shot below, there are:

  • item names in column A
  • quantity sold in column B
  • criteria in cell D1: Pen

COUNTIF 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 2, because there are 2 pen items in the range.

Count Specific Items - COUNTIF

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 represents any number of characters in that position, including zero characters
  • question mark (?) wildcard character represents one characters in that position

Partial Match Formula Example

In the screen shot below, there are:

  • item names in column A
  • quantity sold in column B
  • criteria in cell D1: *Pen*

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.

Count partial matches - COUNTIF and wildcard

6) Count With Multiple Criteria - COUNTIFS

The COUNTIFS function will count cells that match one or more specific criterion. For example, count rows that:

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

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

Count based on multiple criteria - COUNTIFS

COUNTIFS Example

In the screen shot below, there are:

  • item names in column A
  • quantity sold in column B
  • criteria1 in cell D1: Pen
  • criteria2 in cell D2: >=6

COUNTIFS Formula

The following COUNTIFS formula is entered in cell D3, and it refers to the two criteria cells, D1 and D1:

  •  =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

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.

Count in filtered list - SUBTOTAL

SUBTOTAL 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 Number Notes

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

a) Function Numbers 1 to 11

If you use a function number between 1 and 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

If you use a function number between 101 and 111:

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

Tip: For more details on the function numbers, go to the Excel SUBTOTAL function page.

SUBTOTAL Example

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:

  • =SUBTOTAL(102,B2:B6)

The function number is 102, for COUNT.

  • Only numbers will be counted.
  • Both filtered and manually hidden rows will be ignored

Count in filtered list - SUBTOTAL

Video: Count Numbers, All Data, or Blank Cells

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.

Video: COUNTIF Function Examples

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"?

Video: Count With Multiple Criteria

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

  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

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

Subtotal Feature

 

 

Last updated: February 6, 2024 10:43 AM