How to count cells in Microsoft Excel

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.

COUNT result


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)
                    

COUNT formula for areas

Now you can see the total number of numbers for these two ranges of cells.

COUNT result for areas

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)
                    

COUNTBLANK formula in Excel

The result will then be displayed in the cell where you entered the formula.

COUNTBLANK result

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)
                    

COUNTA formula in Excel

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.

COUNTA result

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

COUNTIF text formula in Excel

Note that if your criterion is a word, you need to enclose it in double quotes.

COUNTIF text result

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)
                    

COUNTIF cell reference formula in Excel

In this case, you would not enclose the B2 cell reference in double quotes.

COUNTIF cell reference result


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

Related Posts