Hello friends. Today we are learning how to count cells in Excel. These functions solve a wide range of tasks for representatives of many professions. By performing intermediate calculations, they become the basis for automating your calculations. I know many managers who use counting functions to manage their impressive product line.
If you just need to know the number of values without using them in calculations, it is convenient to view the data in the status bar:
Number of values in the status bar You can select the indicators displayed in a row by clicking on it with the right mouse button.
Status bar customization If you need to use the number of values in further calculations, use the functions described below. For convenience of notation, we will assume that the data array for which the count is kept is . In your formulas, you can use the desired data range instead of the name.
How to count the number of cells in excel
There are two functions for counting the number of cells in Excel:
- ROWS(array) - counts the number of rows in the selected range, regardless of what its cells are filled with. The formula gives a result only for a rectangular array of adjacent cells, otherwise ;
Counting the number of lines - COLUMN(array)- similar to the previous one, but counts the number of array columns
There is no function in Excel to determine the number of cells in an array, but this can be easily calculated by multiplying the number of rows by the number of columns: =NUMROWS(array)*NUMBERCOLUMNS(array).
How to count blank cells in Excel
Counting empty cells The function considers a cell empty if nothing is written in it, or the formula inside it returns an empty string.
How to count the number of values and numbers in Excel
Counting numeric values If you need to determine the number of cells containing values, use the function COUNT(value1, value2,…). Unlike the previous function, it will count not only numbers, but also any combination of characters. If the cell is not empty, it will be counted. If there is a formula in the cell that returns zero or an empty string, the function will also include it in its result.
Count non-empty cells How to count cells with a condition in Microsoft Excel
- An array is a range of cells to be counted. You can only specify a rectangular range of adjacent cells;
- Criteria - the condition by which the selection takes place. Write text conditions and numeric ones with comparison signs in quotation marks. We write equality to the number without quotes. For example:
- ">0" - count cells with numbers greater than zero
- "Excel" - count the cells in which the word "Excel" is written
- 12 - cell count with the number 12
Cell count with condition If you need to consider several conditions, use the function COUNTIFS(array1, criterion1, [array2], [criteria1]…). A function can contain up to 127 array-criteria pairs.
If you use different arrays in one such function, they must all contain the same number of rows and columns.
Counting values by multiple conditions How to determine the most frequently occurring number
To find the number that occurs most often in an array, there is a function in Excel FASHION(number1, number2,…). The result of its execution will be the same number that occurs most often. To determine their number, you can use a combination of summation formulas and array formulas.
If there are several such numbers, the one that appears earlier than others in the list will be displayed. The function only works with numeric data.
Frequently occurring number Here, perhaps, is the entire list of functions that I want to present to you in this post. They are quite enough to solve the most popular cell counting problems. Combine them with other functions (eg ) for maximum results.
In the next article, we will study. Come read, even if you are sure that you know everything about it. I think there will be something just for you!