How to count cells in Excel

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

If you want to count the number of cells which only contain numbers, you can use the COUNT function. The syntax for the formula is: COUNT(value1, value2,...)where is it value1is mandatory and value2it is optional.

You will use value1for your cell references, the range of cells you want to count in. You can use value2(and subsequent arguments) to add a particular number or other range of cells as desired. Let’s take a look at a couple of examples.

To count the number of cells in the range A1 to D7 that contain numbers, type the following and press Enter:

=CONTEGGIO(A1:D7)

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

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

=CONTEGGIO(B2:B7,D2:D7)

Now you will see the total number count for both of these 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 variant of the COUNT function, COUNTBLANK. The syntax for the formula is: COUNTBLANK(value1)where is it value1contains cell references and is required.

To count the number of blank cells in the range A2 to C11, type the following and press Enter:

=CONTA.VUOTE(A2:C11)

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

Count non-empty cells: the COUNT function

Maybe you’d like to do the exact opposite of counting blank cells and instead counting cells that contain data. In this case, you will use the COUNT function. The syntax is COUNTA(value1, value2,...)where is it value1is required and value2it is optional.

Just like the COUNT function above, value1is for cell references and value2it is for the additional ranges you want to include.

To count the number of nonblank cells in the range A2 to C11, type the following and press Enter:

=CONTA.VALORI(A2:C11)

As you can see, the COUNT 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 causes you trouble, it’s easy to hide error values ​​in spreadsheets.

Count cells with specific criteria: the COUNTIF function

If you want to count the number of cells containing specific data, you will use the COUNTIF function. The syntax for the formula is COUNTIF(value1, criteria)where both value1And criteriaare required.

Like the other functions here, value1contains the cell references for the range. Criteriais the element you want to search for and can be a cell reference, word, number, or wildcard. Let’s take a look at a couple of basic examples.

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

=CONTA.SE(C2:C6;"calze")

Note that if your criterion is a word, you must enclose it in quotes.

To count the number of cells in the range B2 to C6 that contain the contents of cell B2, type the following and press Enter:

=CONTA.SE(B2:C6,B2)

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