Microsoft Excel logo on a green background

Excel provides various methods for counting cells including using the Find feature or a filter. But with functions, you can count exactly what you need. From filled cells to blanks, from numbers to text, here’s how to count cells in Excel.

RELATED: How to Count Colored Cells in Microsoft Excel

Count 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 is optional.

You’ll use value1 for your cell references, the range of cells you want to count within. You can use value2 (and subsequent arguments) to add a particular number or another cell range if you like. Let’s look at a couple of examples.

To count the number of cells in the range A1 through D7 that contains numbers, you would type the following and hit Enter:

=COUNT(A1:D7)

COUNT formula in Excel

You then receive the result in the cell containing the formula.

COUNT result

Advertisement

To count the number of cells in two separate ranges B2 through B7 and D2 through D7 that contain numbers, you would type the following and press Enter:

=COUNT(B2:B7,D2:D7)

COUNT formula for ranges

Now you’ll see the total count of numbers for both of those cell ranges.

COUNT result for ranges

Count Blank Cells: The COUNTBLANK Function

Maybe what you want to find is the number of blank cells you have in a particular range. You’ll use a variation 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, you would type the following and press Enter:

=COUNTBLANK(A2:C11)

COUNTBLANK formula in Excel

You’ll then see the result in the cell where you entered the formula.

COUNTBLANK result

For additional COUNTBLANK examples and uses, check out our tutorial for counting blank or empty cells in Excel.

Count Nonblank Cells: The COUNTA Function

Perhaps you’d like to do the exact opposite of counting cells that are blank and instead count cells that contain data. In this case, you would use the COUNTA function. The syntax is COUNTA(value1, value2,...) where value1 is required and value2 is optional.

Advertisement

Just like the COUNT function above, value1 is for your cell references and value2 is for additional ranges you want to include.

To count the number of nonblank cells in the range A2 through C11, you would type the following and hit Enter:

=COUNTA(A2:C11)

COUNTA formula in Excel

As you can see, the COUNTA function counts cells containing any type of data. This includes numbers, text, errors, and empty text or strings. For example, the error in cell C7 is counted.

COUNTA result

If an error is giving you trouble, it’s easy to hide error values in your spreadsheets.

Count Cells With Specific Criteria: The COUNTIF Function

If you would like to count the number of cells containing specific data, you would use the COUNTIF function. The syntax for the formula is COUNTIF(value1, criteria) where both value1 and criteria are required.

Advertisement

Like the other functions here, value1 contains the cell references for the range. Criteria is the item you want to search for and can be a cell reference, word, number, or wildcard. Let’s look at a couple of basic examples.

To count the number of cells in the range C2 through C6 that contain the word “socks,” you would type the following and press Enter:

=COUNTIF(C2:C6,"socks")

COUNTIF text formula in Excel

Notice that if your criterion is a word, you must surround it in double quotes.

COUNTIF text result

To count the number of cells in the range B2 through C6 that contain what’s in cell B2, you would type the following and press Enter:

=COUNTIF(B2:C6,B2)

COUNTIF cell reference formula in Excel

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

COUNTIF cell reference result

Advertisement

For additional COUNTIF examples and uses, take a look at our how-to for using COUNTIF in Excel.

Counting cells in Microsoft Excel is simple once you know these basic functions and how to use them. But if you’re interested in something like highlighting blanks or errors rather than just counting them, we have you covered!

RELATED: How to Highlight Blanks or Errors in Microsoft Excel

Profile Photo for Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio ยป

The above article may contain affiliate links, which help support How-To Geek.