How to use the Excel COUNT function

Anyone who works with large tables often knows that if you try to count specific cells manually, it’s almost impossible to avoid errors. But it doesn’t have to be that way. If you want to know how many cells contain numbers, Excel has a simple function that can help you. In Excel, COUNT immediately tells you how many cells in the selected range contain numbers. We’ll explain how it works.

When working with Excel on a daily basis, sometimes you need to know how many cells in a table contain numbers. For example, if you’re working with revenue results, you may need to check the number of entries. The function can also be used to calculate the sum of numbers from a wide variety of values. Excel’s COUNT function only counts numbers, dates, and numbers turned into text with quotation marks.

All you have to enter for the COUNT function is the range to analyse. Entries are made in the form of cell references. Select a from-to range separated by a colon as the function input.

The advantage of this function is that you can specify multiple ranges. If the cells you want Excel to check are not contiguous, you can simply specify multiple ranges and don’t need to restructure your entire table. You can integrate up to 255 additional ranges of different sizes into the COUNT function.

The COUNT function checks a range for entries that Excel treats as numbers:

This formula gives you the number of cells containing numbers in a single row. You can also expand the search to an entire worksheet or include specific ranges if there are columns in the table you do not want to include.

You can combine COUNT with other functions. For example, you can calculate a mean from the number of cells counted in various ranges.

In this example, you are counting the cells with numbers in multiple ranges (independently) and then calculating the mean value.

Count only lets you count the cells with numbers. It does not include text that is not treated as numbers or Boolean values. This allows you to analyse numbers separately from other content. However, if you want to include all values, COUNTA can help. The function’s structure is identical to the COUNT function.

For example, COUNTA can be used to display the attendance rate at meetings in a given month.

In this example, we want to count all non-blank cells and divide them by the number of all available cells. Attendance is indicated with an “x” in the respective cell. If we now format the resulting cell as a percentage, we can determine the percentage attendance rate.

However, sometimes you want to count all the cells that are empty. You can do that using Excel’s COUNTBLANK function. This function also shares the same structure as the other two functions, but can only include one argument. As a result, you can’t include multiple ranges in one function, you have to aggregate the totals.

If you add COUNTA and COUNTBLANK, the result is always the number of cells in the range analysed. This can be useful, for example, if we go back to our formula for calculating the attendance rate.

In this formula, you no longer need to enter the total number of cells manually. Instead, you calculate it from the total of all blank and non-blank cells.