Effective Techniques for Cell Counting in Microsoft Excel

Quick Links

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.

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)

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

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)

Now you’ll see the total count of numbers for both of those cell 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)

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

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.

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)

/a>

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.

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.

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

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

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)

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

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!

Win Howtos

Effective Techniques for Cell Counting in Microsoft Excel