Count cells in Excel using COUNTIF and COUNTIF functions

Very often, when working in Excel, you need to count the number of cells on a worksheet. These can be empty or filled cells containing only numeric values, and in some cases, their contents must meet certain criteria. In this tutorial, we will take a detailed look at two main Excel functions for counting data − CHECK и COUNTIF, as well as get acquainted with the less popular – SCHETZ, COUNTBLANK и COUNTIFS.

CHECK()

statistical function CHECK counts the number of cells in the argument list that contain only numeric values. For example, in the figure below, we have counted the number of cells in a range that consists entirely of numbers:

In the following example, two range cells contain text. As you can see, the function CHECK ignores them.

Count cells in Excel using COUNTIF and COUNTIF functions

But cells containing date and time values ​​are taken into account:

Count cells in Excel using COUNTIF and COUNTIF functions

Function CHECK can count the number of cells in several non-contiguous ranges at once:

Count cells in Excel using COUNTIF and COUNTIF functions

If you need to count the number of non-empty cells in a range, you can use the statistical function SCHETZ. Cells containing text, numeric values, date, time, and boolean values ​​TRUE or FALSE are considered non-empty.

Count cells in Excel using COUNTIF and COUNTIF functions

Solve the inverse problem, i.e. count the number of empty cells in Excel, you can use the function COUNTBLANK:

Count cells in Excel using COUNTIF and COUNTIF functions

COUNTIF()

statistical function COUNTIF allows you to count the cells of an Excel worksheet using various types of conditions. For example, the formula below returns the number of cells that contain negative values:

Count cells in Excel using COUNTIF and COUNTIF functions

The following formula returns the number of cells whose value is greater than the contents of cell A4.

Count cells in Excel using COUNTIF and COUNTIF functions

COUNTIF allows you to count cells containing text values. For example, the following formula returns the number of cells containing the word “text” and is case insensitive.

Count cells in Excel using COUNTIF and COUNTIF functions

Boolean function condition COUNTIF may contain wildcards: * (asterisk) and ? (question mark). An asterisk stands for any number of arbitrary characters, while a question mark stands for one arbitrary character.

For example, to count the number of cells containing text that starts with a letter Н (case insensitive), you can use the following formula:

Count cells in Excel using COUNTIF and COUNTIF functions

If you need to count the number of cells that contain exactly four characters, then use this formula:

Count cells in Excel using COUNTIF and COUNTIF functions

Function COUNTIF allows you to use even formulas as a condition. For example, to count the number of cells with values ​​greater than the average, you can use the following formula:

Count cells in Excel using COUNTIF and COUNTIF functions

If one condition is not enough for you, you can always use the statistical function COUNTIFS. This function allows you to count cells in Excel that satisfy two or more conditions at once.

For example, the following formula counts cells whose values ​​are greater than zero but less than 50:

Count cells in Excel using COUNTIF and COUNTIF functions

Function COUNTIFS allows cells to be counted using a condition И. If you want to calculate the quantity with the condition OR, you need to use several functions COUNTIF. For example, the following formula counts cells that start with a letter А or with a letter К:

Count cells in Excel using COUNTIF and COUNTIF functions

Excel functions for counting data are very useful and can come in handy in almost any situation. I hope that this lesson has revealed all the secrets of functions for you. CHECK и COUNTIF, as well as their closest associates – SCHETZ, COUNTBLANK и COUNTIFS. Come back to us more often. All the best to you and success in learning Excel.

1 Comment

  1. Å¡ta je ovo!

Leave a Reply