How to calculate the sum of values ​​in the cells of an Excel table, for sure, every user who works in this program knows. This will help the SUM function, which is placed in a prominent place in the latest versions of the program, 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 the given 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 in several ways at once.

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

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

Perhaps this is the easiest method that is suitable for working with text and numeric data. But he is not able to work with conditions.

Using this method is extremely simple: we select the data array of interest (in any convenient way). The result will immediately appear in the status bar (Quantity). All cells are included 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 areas of interest in the table or even specific cells and see the result. But this is convenient only when working with small data arrays. For large tables, there are other ways, which we will discuss later.

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

Sometimes it happens that by default the “Quantity” indicator is not included in the status bar, but this can be easily fixed:

1. Right click on the status bar.
2. In the list that opens, pay attention to the line “Quantity”. If there is no checkmark next to it, then it is not included in the status bar. Click on a line to add it.
3. Everything is ready, from now on this indicator will be added to the status bar of the program.

Method 2: Using the COUNTA Function

This function allows you to do the same, but with one significant difference – the result will be fixed. The remaining 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 the specified range (empty cells are not counted). The function formula may look different:

• =COUNTA(cell1,cell2,…cellN)
• =COUNTA(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. Please note that the number of function arguments is limited at around 255.

Let’s try to apply the COUNT3 function with an example:

1. Select the cell where the total will display the result of the calculation.
2. Go to the “Formulas” tab and click the “Insert Function” button.You can also click on the “Insert Function” icon next to the formula bar.
3. In the menu that opens (Function Wizard), select the “Statistical” category, then look for the desired COUNT3 function in the list, select it and click OK to start setting it up.
4. In the “Function Arguments” window, specify the desired cells (by listing them or by specifying a range) and click on the OK button. You can set a range with or without a title.
5. The result of the calculation will be displayed in the cell we selected, which was originally required. All cells with any data (except for empty ones) are taken into account.

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 taken into account by this function. Otherwise, COUNT is almost identical to COUNTA from the previously discussed method.

This is how the COUNT function formula looks like:

• =COUNT(cell1,cell2,…cellN)
• =COUNT(cell1:cellN)

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

1. Select the cell where the result of counting values ​​will be saved and displayed.
2. We go to the Function Wizard in any convenient way, select the required line COUNT in the “Statistical” category and click OK.
3. In the “Function Arguments” set the range of cells or list them. Next, click OK.
4. The result will be displayed in the selected cell. The COUNT function will ignore all cells with empty content or text values. Thus, only those cells that contain numeric data will be counted.

Method 4: COUNTIF Operator

Finally, we come to a function that is able to make a calculation according to certain conditions. Operator COUNTIF created just for this task. All cells without data and those that do not meet user-specified parameters are ignored by this function.

The COUNTIF syntax is typical for all conditional operators:

=COUNTIF(range, criteria)

Range – the area of ​​cells within which you need to find the number of matches according to a given condition.

A criterion is a specific condition that the function is looking for a match on. The condition is indicated in quotation marks, can be specified as an exact match with the entered number or text, or as a mathematical comparison, specified by the signs “not equal” (“<>“), “greater than” (“>”) and “less than” (“<"). It is also possible to add the conditions "greater than or equal to" / "less than or equal to" ("=>/=<").

Let’s analyze the application of the function COUNTIF:

1. Let’s, for example, determine how many times the word “running” appears in the sports column. Go to the cell where you want to display the final result.
2. Using one of the two methods described above, we enter the Function Wizard. In the list of statistical functions, select COUNTIF and click OK.
3. The argument window is somewhat different from those we saw when working with COUNTA and COUNT. Fill in the arguments and click OK.
   • In the “Range” field, specify the area of ​​the table that will participate in the calculation.
   • Specify a condition in the Criteria field. We need to determine the frequency of occurrence of cells containing the value “running”, therefore we write this word in quotes. We click OK.
   • 
4. The COUNTIF function will count and display in the selected cell the number of matches with the given word. In our case, there are 16.

For a better understanding of how to work with the COUNTIF function, let’s try to change the condition:

1. Let’s now determine how many times any other values ​​uXNUMXbuXNUMXbappear in the same column, except for the word “running”.
2. Select a cell, go to the Function Wizard, find the COUNTIF operator, click OK.
3. In the “Range” field, enter the coordinates of the same column as in the example above. In the “Criterion” field, add a not equal sign (“<>“) before the word “running”.
4. After pressing the OK button, we get a number that tells us how many cells in the selected range (column) that do not contain the word “run”. This time the number is 17.

Finally, you can analyze the work with numerical conditions containing the signs “greater than” (“>”) or “less than” (“<"). Let's, for example, find out how many times a value greater than 350 occurs in the "Sold" column.

1. We follow the usual steps to insert the COUNTIF function into the desired resulting cell.
2. In the range field, specify the required range of column cells. Set the condition “>350” in the field “Criterion” and click OK.
3. In a pre-selected cell, we get a total – 10 cells contain values ​​​​greater than the number 350.

Method 5: Using the COUNTIFS Operator

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

For example, we need to calculate the number of goods that are sold more than 300 pieces, as well as goods whose cost is more than 6000 rubles.

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

1. In the Function Wizard, in a familiar way, we find the COUNTIFS operator, which is still in the same category “Static” and insert it into the cell to display the result by pressing the OK button.
2. It seems that the function settings window does not differ from COUNTIF, but as soon as we enter the data of the first condition, fields for entering the second one will appear.
   • In the “Range 1” field, enter the coordinates of the column containing sales data in pcs. In the “Condition 1” field, according to our task, we write “> 300”.
   • In “Range 2” we indicate the coordinates of the column that contains data on prices. As “Condition 2”, respectively, we indicate “> 6000”.
3. We press OK and we get a number in the final cell that tells us how many times in the selected ranges there were cells with the parameters we specified. In our example, the number is 14.

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 will be extremely useful COUNTBLANK, which will ignore all cells except empty ones.

The syntax of the function is extremely simple:

=COUNTBLANK(range)

The procedure is practically no different from the above:

1. Select the cell where we want to display the final result by counting the number of empty cells.
2. We go into the Function Wizard, among the statistical operators, select “COUNTNUT” and click OK.
3. In the “Function Arguments” window, specify the desired range of cells and click on the OK button.
4. The result will be displayed in the cell we selected in advance. Only empty cells will be taken into account and all others will be ignored.

Conclusion

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