There are quite a few ways to determine the number of lines. When using them, various tools are used. Therefore, you need to look at a specific case in order to choose a more suitable option.

Method 1: the pointer in the status bar

The easiest way to solve the problem in the selected range is to see the quantity in the status bar. To do this, simply select the desired range. It is important to take into account that the system counts each cell with data as a separate unit. Therefore, to avoid double counting, since we need to know the number of rows, we select only one column in the study area. In the status bar after the word "Quantity" to the left of the buttons for switching display modes, an indication of the actual number of filled elements in the selected range will appear.

True, this also happens when there are no completely filled columns in the table, while each row contains values. In this case, if we select only one column, then those elements that have no values ​​in that particular column will not be included in the calculation. Therefore, we immediately select a completely specific column, and then, holding down the button Ctrl click on the filled cells in those lines that turned out to be empty in the selected column. In this case, we select no more than one cell per row. Thus, the status bar will display the number of all lines in the selected range, in which at least one cell is filled.

But there are also situations when you select filled cells in rows, and the display of the quantity on the status bar does not appear. This means that this function is simply disabled. To enable it, right-click on the status bar and in the menu that appears, check the box next to the value "Quantity"... The number of selected rows will now be displayed.

Method 2: using a function

But, the above method does not allow fixing the counting results in a specific area on the sheet. In addition, it provides the ability to count only those rows in which there are values, and in some cases it is necessary to count all the elements in the aggregate, including empty ones. In this case, the function will come to the rescue ROWS... Its syntax is as follows:

ROWS (array)

It can be driven into any empty cell on the sheet, and as an argument "Array" substitute the coordinates of the range in which you want to count.

To display the result on the screen, it will be enough to press the button Enter.

Moreover, they will even be counted completely blank lines range. It is worth noting that, unlike the previous method, if you select an area that includes several columns, then the operator will count only lines.

For users who have little experience with Excel formulas, it is easier to work with this operator through Function wizard.

Method 3: apply filter and conditional formatting

As you can see, there are several ways to find out the number of lines in a selection. Each of these methods is appropriate to apply for specific purposes. For example, if you need to fix the result, then in this case the option with a function is suitable, and if the task is to count the lines that meet a certain condition, then conditional formatting will come to the rescue with subsequent filtering.

Example 1: data from the status bar

The number of lines in the selected range is displayed in the status bar area. Select the desired range of values ​​Excel will automatically calculate the number of rows displayed on the status bar. It should be remembered that in this case only cells with values ​​are taken into account. In the example below, to calculate the number of rows, you need to select one column and the number of positions in the selected range will be displayed at the bottom of the window.

There are more difficult situations. For example, if the columns are not completely filled, i.e. there are empty positions in the column. If you select only a column, empty rows will not be counted. Let's take a look at the example below. Empty cells from the first column correspond to the values ​​of the cells of the second. Select the entire first column and, while holding down the "Ctrl" key, click on the corresponding cells with values ​​from the second column. At the bottom of the window, the entire number of rows of the selected range will be displayed, where the cells have no values.

If you have done everything as described above, and the quantity is not displayed, then this means that you need to enable the corresponding option. To do this, right-click on the status bar and select "Quantity".

Example 2: Calculation Using Formulas

The above example does not provide an option to save the counting results and display them in a cell on a worksheet. Moreover, it is often necessary to take into account empty positions as well. For this, it is convenient to use the ROWS formula.

The formula is: = ROWS (array of values).

With this function, you can insert the desired cell and, instead of an array, specify the range of cells for which you want to calculate the number of positions.

Then press Enter.

When using this formula, rows without values ​​are also counted. In addition, even if an area is selected that includes more than one column, the function will count only rows.

For some users, it will be more convenient to use this formula through the Formula Builder.

1) Set the cursor to the position where we will insert the formula, that is, where the result will be displayed and click on the "Insert formula" icon.

2) The Formula Builder window will open. Set the "Category" value to "References and Arrays", you can also select "Complete alphabetical list". Find the function "ROWS" and click "OK"

3) In the window for selecting formula arguments, place the cursor on after the array and select the area with the desired range of values. After the cells of the range appear in the function arguments field, click "OK"

4) After doing the above steps at the specified position, Excel will automatically calculate the number of rows in the range. The calculations will continue until you remove the formula from the cell yourself.

Example 3: using formatting and filtering

In more complex cases, when it is necessary to count only positions that fall under the specified conditions. For such cases, conditional formatting is used and then filters are applied.

1) Select the range of values ​​for which it is necessary to fulfill the conditions.

2) Go to the main panel and select "Styles" then click on the icon "Conditional Formatting". In the conditional formatting, select the "Cell Selection Rules" and for our case, select the "Greater ..." rule.

3) A dialog for specifying conditions appears. On the left, a value is indicated above which, the cells will be colored with the specified color. The color on the right of the part is set. After selection, click "OK"

4) As you can see below, the items that match the conditions are colored with the specified color. While on the main panel, select the entire range and click the "Sort and Filter" menu item and select "Filter"

In the process of working in the spreadsheet editor Excel, the user may need to count the number of cells with data. This can be useful in different cases, but the main thing is that the program has this feature. This article will tell you how to count the number of filled cells with text in Excel. Moreover, there will be five ways to choose from, so it is recommended to read the article to the end in order to choose the best one for yourself.

We perform cell counting

Before starting the story about how to count the number of filled cells in Excel, it is worth outlining the methods themselves in general. Firstly, you can use a special counter located on the status bar, but in some cases it will need to be turned on first. This will be discussed below. Secondly, you can use special functions. With their help, you can flexibly configure the counting parameters. All this will be discussed right now.

Method 1: counter on the bottom panel

And we will start with instructions on how to count the number of filled cells in a column, row, or in a certain range in Excel, which implies the use of a special counter. This is perhaps the easiest method to accomplish the task at hand.

To count the cells, you first need to select the desired range. This is done simply - you need to hold down the left mouse button and drag the cursor over the desired area. Immediately after that, at the bottom, the item "Quantity" will appear, opposite which the number of cells with data will be displayed. It is worth noting that it only appears when you select more than two cells with some information.

It is also worth focusing on the fact that this parameter on the bottom panel can be disabled, although by default its display is provided. In any case, if after selecting a range of cells with data the required parameter does not appear, do the following manipulations:

1. Move your cursor over the status bar.
2. Click the right mouse button.
3. In the menu that appears, check the box next to the item "Quantity".

Immediately after that, the corresponding option will be enabled and you can use it without any problems. This was the first way, in Excel, to count the number of filled cells in a row, column or a whole range.

Method 2: COUNTS function

You can also count the number of filled cells in Excel using the COUNTS formula. In some cases, it can be very useful. For example, it is perfect when the number of filled cells needs to be displayed directly in a cell. Thus, you can observe changes in the readings immediately.

1. Select the cell where the counting result will be displayed.
2. Click on the "Insert Function" button located to the left of the formula bar.
3. In the appeared window of the Function Wizard, find the line "COUNT" in the list, select it and click the "OK" button.
4. In the second step, in the Value parameters, specify the area or areas to be counted.
5. Click OK.

Immediately after that, the selected cell will display the number of cells in the specified areas in which any data is recorded.

If desired, you can perform the same actions by entering the COUNT function directly into the function string. Its syntax is as follows:

COUNT (value1; value2; ...).

That is, you initially need to enter the name of the function itself (= COUNT), and then in parentheses, separated by a semicolon, write the range of cells in which you want to count.

Method 3: COUNT function

Continuing to chew on the topic of how to count the number of cells in Excel, let's touch on the COUNT function. As you can understand, it is very similar to the previous one, but it makes it possible to count cells in which only numerical values ​​are entered. Here's how to use it:

1. Select a cell on the sheet in which you want to display data.
2. Click the Insert Function button.
3. In the window that appears, select the ACCOUNT function from the list and click the "OK" button.
4. Before you, like last time, a window will appear in which you need to enter values, that is, a range or several ranges of cells.
5. Once done, click the "OK" button.

After performing all these actions, a number corresponding to the number of cells with numbers in the selected range will appear in the initially selected cell.

Method 4: COUNTIF function

This method how to count the number of filled cells in Excel, again, implies working only with numerical data, but this time you can set some conditions for performing the counting. Let's take a closer look at how to use the COUNTIF function now:

1. As well as in previous ways, position the cursor on the cell where the function will be executed.
2. Call the Function Wizard window.
3. Select the COUNTIF line from the list and press the OK button.
4. A rather familiar window will appear in which you need to enter the variables of the function. As you can see, in addition to the range, you need to specify a criterion. For example, you can enter "> 400". This will mean that those cells with values ​​greater than 400 will be counted.
5. Further, you can specify more ranges and criteria for them.
6. When you have finished entering the parameters, click the "OK" button.

Once you do this, the selected cell will display the number of cells with data matching the criteria.

Method 5: COUNTIFS function

The last, fifth way, as in Excel, to count the number of filled cells, is essentially no different from the previous one. It is used when many variables need to be calculated. That is, you can specify 126 conditions. Drive detailed instructions There is no point in using the COUNTIFS function, since it completely repeats the previous one.

So we figured out how to count data cells. As you can see, there are many ways. If you do not need it often, then it is best to use the counter on the bottom panel. If you need to inspect the number of cells constantly, then you need to use one of the four above formulas.

How to calculate the sum of values ​​in the cells of an Excel spreadsheet, for sure, every user who works in this program knows. This will help the SUM function, which is rendered in latest versions programs in a prominent place, since, perhaps, it is used much more often than others. But sometimes the user may face a slightly different task - to find out the number of values ​​with specified parameters in a certain column. Not their sum, but a simple answer to the question - how many times does the Nth value occur in the selected range? In Excel, you can solve this problem with several methods at once.

Which of the following methods is most suitable for you depends a lot on your purpose and the data you are working with. Some operators are suitable only for numerical data, others do not work with conditions, and still others do not record the result in the table. We will tell you about all the methods, among which you will definitely find the one that is best for you.

Method 1: display the number of values ​​in the status bar

This is probably the easiest method for working with text and numeric data. But he is incapable of working with conditions.

It is extremely simple to use this method: select the data array of interest (in any convenient way). The result will immediately appear in the status bar (Quantity). All cells are involved in the calculation, except for empty ones.

We emphasize once again that this method takes into account cells with any values. In theory, you can manually select only the parts of the table of interest or even specific cells and see the result. But this is convenient only when working with small data sets. For large tables, there are other methods, which we will discuss next.

Another disadvantage of this method is that the result is saved only until we deselect the cells. Those. you will have to either remember or write down the result somewhere separately.

Sometimes it happens that the "Quantity" indicator is not included in the status bar by default, but this is easily fixable:

Method 2: using the COUNT function

This function allows you to do the same, but with one significant difference - the result will be recorded. The rest of the disadvantages of the previous method are preserved, i.e. ACCOUNT3 does not work with conditions.

Function ACCOUNT3 performs the task of counting all filled cells in a given range (empty ones are not counted). The function formula may look different:

• = COUNT (cell1; cell2; ... cellN)
• = COUNT (cell1: cellN)

In the first case, the function will count all the listed cells. In the second, it will determine the number of non-empty cells in the range from cell 1 to cell N. Note that the number of function arguments is limited to 255.

Let's try to use the COUNT3 function with an example:

Method 3: using the COUNT function

The COUNT function is suitable if you work exclusively with numbers. Cells filled with text values ​​will not be counted by this function. The rest of the COUNT is almost identical to the COUNT from the previously discussed method.

This is the formula for the COUNT function:

• = COUNT (cell1; cell2; ... cellN)
• = COUNT (cell1: cellN)

The algorithm of actions is also similar to the one we discussed above:

Method 4: COUNTIF operator

Finally, we come to a function that is capable of doing a count based on certain conditions. Operator COUNTIF created for just this task. All cells without data and those that do not correspond to user-specified parameters are ignored by this function.

The COUNTIF syntax is typical for all conditional operators:

= COUNTIF (range, criterion)

Range is an area of ​​cells, within which it is necessary to find the number of matches for a given condition.

A criterion is a specific condition that a function is looking for to match. The condition is indicated in quotation marks, it can be specified as an exact match with the entered number or text, or as a mathematical comparison, specified by signs "not equal" ("<>")," More "("> ") and" less "("<»). Также предусмотрена возможность добавить условия «больше или равно» / «меньше или равно» («=>/=<»).

Let's look at the use of the function COUNTIF:

For a better understanding of working with the COUNTIF function, let's try to change the condition:

Finally, you can parse the work with numerical conditions containing signs "greater than" (">") or "less" ("<»). Давайте, например, выясним сколько раз в столбце “Продано” встречается значение больше 350.

Method 5: using the COUNTIFS operator

COUNTIF is a function that is completely similar to COUNTIF, but allows you to enter several ranges with the same or different conditions.

Let's see how to do this using the WHATIFS function:

Method 6: COUNTBLANK function

In some cases, we may be faced with the task of counting only empty cells in the data array. Then the function COUNTBLANK which will ignore all cells except empty ones.

The syntax is very simple function:

The procedure is practically the same as the ones listed above:

Conclusion

Microsoft Excel is an extremely powerful tool that can solve many problems of all levels of complexity. Even in the simplest task of counting the number of values, the program can greatly facilitate the user's life, and in several different ways. Which method of calculating the number of values ​​listed in this article to stop depends on your goals and objectives.

Hello dear readers.

Before starting this topic, I would like to advise you on an excellent educational product on the topic of Excel, called « Unknown Excel» , everything is clearly and qualitatively stated there. I recommend.

Now let's get back to the topic.

You are probably a representative of one of the professions in which you cannot do without the Excel program. After all, it allows you to make calculations, make lists, tables and diagrams, fill out diaries and solve many other tasks related to numbers.

However, not everyone who works with this application knows its full functionality and knows how to apply it in practice. Are you one of them? Then you've come to the right place. In particular, today we will analyze how to count the number of cells with a value in excel. There are several ways to do this. They depend on what kind of content you need to count. Let's analyze the most popular ones.

The fastest way

The simplest, but at the same time superficial, way is to count items in the status bar. Their number is displayed in the lowermost pane of the open window.

If you want to set certain simple parameters for calculations, open the status bar settings. This can be done by right-clicking on it. In the window that appears, pay attention to the part where it says "Average", "Amount", "Number of numbers", "Minimum", "Maximum" and "Sum".

Select the option you want and learn more about what your table contains.

Count cells in rows and columns

There are two ways to find out the number of sections. The first one makes it possible to count them line by line in the selected range. To do this, you must enter the formula = ROWS (array) in the appropriate field. In this case, all cells will be counted, not just those containing numbers or text.

The second option - = NUMBERCOLUMN (array) - works by analogy with the previous one, but counts the sum of the sections in the column.

Count numbers and values

I will tell you about three useful things to help you work with the program.

We set the Excel conditions

An array is a range of items among which accounting is kept. It can only be a rectangular continuous collection of adjacent cells. The criterion is considered to be just the condition according to which the selection is performed. If it contains text or numbers with comparison signs, we enclose it in quotes. When the condition just equates to a number, the quotes are not needed.

Understanding the criteria

Examples of criteria:

• "> 0" - cells with numbers from zero and higher are counted;
• "Product" - sections containing this word are counted;
• 15 - you get the sum of the elements with the given digit.

For greater clarity, I will give a detailed example.

Logic problems

Do you want to set logical parameters to Excel? Use wildcards * and ?. The first one will denote any number of arbitrary characters, and the second one - only one.

For example, you need to know how many case-insensitive T cells in a spreadsheet have. We set the combination = COUNTIF (A1: D6; "T *"). Another example: you want to know the number of cells containing only 3 characters (any) in the same range. Then we write = COUNTIF (A1: D6; "???").

Averages and multiple formulas

Even a formula can be specified as a condition. Want to know how many sections you have that are above average in a certain range? Then you should write the following combination in the formula bar = COUNTIF (A1: E4; ">" & AVERAGE (A1: E4)).

If you need to count the number of filled cells by two or more parameters, use the COUNTIFS function. For example, you are looking for sections with data greater than 10, but less than 70. You write = COUNTIFS (A1: E4; "> 10"; A1: E4; "<70»).

In addition, you have the opportunity to set AND / OR conditions. Only in the second case you will have to use several rules at once. Look: you need to find cells in which words begin with the letter B or P - write = COUNTIF (A1: E4; "B *") + COUNTIF (A1: E4; "P *").

Perhaps, at first glance, the above instructions do not seem entirely clear to you. But having applied them several times in practice, you will see that they greatly simplify and improve the work with Excel.