Home > Formulas > Statistical > Count
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
Author: Debra Dalgleish
- Video: 7 Ways to Count in Excel
- --1) Count Numbers - COUNT
- --2) Count All Data - COUNTA
- --3) Count Empty Cells - COUNTBLANK
- --4) Count Specific Items - COUNTIF
- --5)
- --6) Count with Multiple Criteria - COUNTIFS
- --7) Count in Filtered List - SUBTOTAL
- Video: Count Numbers, Data, Blank Cells
- Video: Count with Criteria
- Video: Count With Multiple Criteria
- Get Count Functions Sample Files
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 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 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.
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.
3) Count Empty Cells - COUNTBLANK
The COUNTBLANK function will count cells that are empty (blank), and cells that contain an empty string
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.
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".
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.
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 (*) 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.
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.
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.
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.
- Tip: For more ways to count rows in a filtered list, go to the Excel Filtered Rows Count or Sum page.
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.
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
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
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
- 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.
- 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.
