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.
But cells containing date and time values are taken into account:
Function CHECK can count the number of cells in several non-contiguous ranges at once:
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.
Solve the inverse problem, i.e. count the number of empty cells in Excel, you can use the function COUNTBLANK:
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:
The following formula returns the number of cells whose value is greater than the contents of cell A4.
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.
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:
If you need to count the number of cells that contain exactly four characters, then use this formula:
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:
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:
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 К:
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.
šta je ovo!