How to calculate the number of values ​​in the Excel column

In some cases, the user is set to the task of not counting the amount of the values ​​in the column, but counting their quantity. That is, simply speaking, you need to calculate how many cells in this column are filled with certain numeric or text data. In Excel, there are a number of tools that are able to solve the specified problem. Consider each of them individually.

As we see, unlike the previous method, this option proposes to output the result into a specific element of the sheet with its retaining it there. But, unfortunately, the account function still does not allow setting the conditions for the selection of values.

Lesson: Wizard Functions in Excel

Method 3: Operator Account

Using the operator, the account can be calculated only by numeric values ​​in the selected column. It ignores text values ​​and does not include them in a general result. This feature also refers to the category of statistical operators, as well as the previous one. Its task is to count the cells in the dedicated range, and in our case in the column, which contains numerical values. The syntax of this feature is almost identical to the previous operator:

= Account (value1; value2; ...)

As you can see, the arguments of the bill and the account are completely the same and represent references to cells or ranges. The difference in the syntax is only in the name of the operator itself.

1. We highlight the element on the sheet where the result will be displayed. Click already familiar to us the "Insert function" icon.



2. After starting the wizard of the functions, again move to the category "Statistical". Then we highlight the name "Account" and click on the "OK" button.



3. After the operator's arguments window is running, the account should be recorded in its field. In this window, as in the previous function window, it can also be represented to 255 fields, but, as it last time, we will need only one of them called "Value1". We enter the column coordinate in this field, over which we need to perform an operation. We do it in the same way that this procedure was performed for the function of the account: Install the cursor in the field and select the table column. After the address of the column was listed in the field, click on the "OK" button.



4. The result will immediately be withdrawn into the cell, which we defined for the content of the function. As you can see, the program calculated only the cells that contain numeric values. Blank cells and elements containing text data did not participate in the calculation.

Lesson: Function account in Excel

Method 4: Operator Council

In contrast to the previous ways, the use of the service operator allows you to specify the conditions that meet the values ​​that will take part in the counting. All other cells will be ignored.

The operator of the member is also ranked as the Excel statistical group. Its the only task is to count non-empty elements in the range, and in our case in the column that meet the specified condition. The syntax of this operator differs markedly from the previous two functions:

= Schedules (Range; Criterion)

The argument "Range" is presented in the form of reference to a specific array of cells, and in our case on the column.

The argument "Criterion" contains a given condition. This can be both an exact numeric or text value and the value specified by the "greater" signs (>), "less" (

We calculate how many cells with the name "meat" are located in the first column of the table.

1. We highlight the element on the sheet where the display of ready-made data will be made. Click on the "Insert function" icon.



2. In the functions wizard, we make the transition to the category "Statistical", we allocate the name of the countequence and click on the "OK" button.



3. Activation of the arguments of the arguments of the function of the meter is made. As you can see, the window has two fields that correspond to the function arguments.

   In the field "Range" in the same way, which we have already described more than once, we introduce the coordinates of the first column of the table.

   In the "Criterion" field, we need to specify the calculation condition. Enter the word "meat" there.

   After the above settings are made, click on the "OK" button.



4. The operator makes calculations and gives the result on the screen. As you can see, in the selected column in 63 cells, the word "meat" is contained.

Let's change the task slightly. Now we consider the number of cells in the same column that do not contain the word "meat".

1. We select the cell where we will output the result, and the previously described method we call the arguments of the operator's arguments.

   In the "Range" field, we introduce the coordinates of the same first column of the table, which was processed earlier.

   In the "Criterion" field, we introduce the following expression:

   Meat

   That is, this criterion specifies the condition that we count all the elements filled with data that do not contain the word "meat". The sign "" means in Excele "not equal."

   After entering these settings in the argument window, press the "OK" button.



2. In the preset cell immediately displays the result. He reports that in the highlighted column there are 190 elements with data that do not contain the word "meat".

Now let's produce in the third column of this table counting all the values ​​that are more than the number 150.

1. We highlight the cell to display the result and make the transition to the arguments of the function of the function.

   In the "Range" field, we introduce the coordinates of the third column of our table.

   In the "Criterion" field, write the following condition:

   > 150.

   This means that the program will only count the column elements that contain numbers exceeding 150.

   Further, as always, press the "OK" button.



2. After counting Excel displays the result in a predetermined cell. As we see, the selected column contains 82 values ​​that exceed the number 150.

Thus, we see that in Excel there are a number of ways to calculate the number of values ​​in the column. Selecting a specific option depends on the specific purposes of the user. Thus, the indicator on the status bar allows only to see the number of all values ​​in the column without fixing the result; The account function provides the ability to fix them in a separate cell; The account operator calculates only the elements containing numeric data; And with the help function, you can set more complex calculation conditions for the elements.