Excel offers several methods of counting cells including using the search function or a filter. But functions let you count exactly what you need. From filled cells to blank cells, from numbers to text, this is how you count cells in Excel.
table of contents
Counting cells with numbers: The COUNT function
Counting Empty Cells: The COUNTBLANK Function
Counting non-empty cells: the COUNTA function
Counting cells with specific criteria: The COUNTIF function
How to Count Colored Cells in Microsoft Excel
Counting cells with numbers: The COUNT function
If you want to count the number of cells that contain only numbers, you can use the COUNT function. The syntax for the formula is: COUNT(value1, value2,...) Where value1 is required and value2 it’s optional.
You will use value1 for your cell references, the cell range in which you want to count. You can use value2 (and subsequent arguments) to add a specific number or other range of cells, if you want. Let’s look at a few examples.
To count the number of cells in the range A1 through D7 that contain numbers, type the following and press Enter:
=COUNT(A1:D7)
You will then get the result in the cell with the formula.
To count the number of cells in two separate areas B2 through B7 and D2 through D7 that contain numbers, type the following and press Enter:
=COUNT(B2:B7,D2:D7)
Now you can see the total number of numbers for these two ranges of cells.
Counting Empty Cells: The COUNTBLANK Function
You might want to find out the number of blank cells in a specific range. You are using a variant of the COUNT function, COUNTBLANK. The syntax for the formula is: COUNTBLANK(value1) Where value1 contains the cell references and is required.
To count the number of blank cells in the range A2 through C11, type the following and press Enter:
=COUNTBLANK(A2:C11)
The result will then be displayed in the cell where you entered the formula.
For more COUNTBLANK examples and uses, see our tutorial on Counting Blank or Empty Cells in Excel.
Counting non-empty cells: the COUNTA function
You might want to do the exact opposite of counting blank cells and instead counting cells that contain data. In that case you would use the COUNTA function. The syntax is COUNTA(value1, value2,...) Where value1 is required and value2 it’s optional.
As the COUNT function above , value1 is for your cell references and value2 is for additional areas that you want to include.
To count the number of non-blank cells in the range A2 through C11, type the following and press Enter:
=COUNTA(A2:C11)
As you can see, the COUNTA function counts cells with any data type. These include numbers, text, errors, and blank text or strings. To the example, the error is counted in cell C7.
If you have problems with an error, you can easily hide error values in your tables.
Counting cells with specific criteria: The COUNTIF function
If you want to count the number of cells with specific dates, use the COUNTIF function. The syntax for the formula is COUNTIF(value1, criteria) where both value1 and criteria are required.
Like the other functions here, value1 contains the cell references for the area. Criteria is the item you want to search for and can be a cell reference, word, number, or placeholder. Let’s look at a few basic examples.
To count the number of cells in the range C2 through C6 that contain the word “socks”, type the following and press Enter:
=COUNTIF(C2:C6,"socks")
Note that if your criterion is a word, you need to enclose it in double quotes.
To count the number of cells in the range B2 through C6 that contain the contents of cell B2, type the following and press Enter:
=COUNTIF(B2:C6,B2)
In this case, you would not enclose the B2 cell reference in double quotes.
For more COUNTIF examples and uses, see our guide on using COUNTIF in Excel.
Counting cells in Microsoft Excel is easy if you know and use these basic functions. But if you’re interested in highlighting gaps or errors instead of just counting them, we’re here for you!
How to mark spaces or errors in Microsoft Excel
