How to calculate the number of values ​​in the Excel column. 6 ways to calculate the number of values ​​in the Ekstel column

Often, in front of users of the table editor, the question arises that relates to counting the number of indicators in the column. In other words, it is necessary to calculate the number of fields in the column filled with a specific text or numerical information. The table editor has many variations that allow you to perform this feature.

The calculation procedure in the column

Depending on the purposes of the user in the table editor, you can implement the counting of all data in the column. We will analyze each method in more detail.

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

This method is the easiest to use. It implements counting numeric and text information, but cannot be used when working with the conditions. General Action Algorithm:

1. Select the required data area.
2. We look into the line of the state.
3. We learn the number of values.

one

The method is convenient to use only when working with average data volumes, otherwise you need to use other options. Another minus of the method - the result is visible only at the time of isolation, so you need to be removed or memorized or fixed elsewhere. Step-by-step guide to incorporate the "Number" indicator:

1. Click PCM on the status bar.
2. The screen displays the list. Here it is necessary to check whether the mark is about the "Quantity" parameter. If it is not, then it must be put by clicking on the left mouse button.

2.

1. Ready! Now the "number" indicator is located in the status bar of the table processor.

Method 2: Application of the RC function

This operator implements the same thing as the previous method, but at the same time additionally fixes the result. Other flaws remain, since the function of the invoice cannot work with the conditions.

There are two common types of operator:

1. = Bar (cell1; cell2; ... cell).
2. = Blossom (cell1: cell).

In the 1st form, the operator calculates each selected cell. In the 2nd form, the number of filled fields is calculated in the range from the cell number 1 to the cell with the number N. Step by step guide:

1. We produce a selection of the cell in which the counting will be displayed.
2. Moving in the subsection "Formulas". Click "Insert a function."

3.

1. Alternative option - pressing the "Insert function" icon, which is located next to the line for a set of formulas.

four

1. A small "Insert Functions" appeared on the screen. Open the list located next to the "Category" inscription. In the discontinued list, select the "statistical" element. In the "Select Function:" field, we find the operator of the invoice and click on it by the LKM. After all manipulations, click on the "OK" element.

five

1. The display displays the window to write operator arguments. Here it is necessary by transferring the cells or the input range to set the arguments. It is worth noting that the range can be set with the title. After all manipulations, click on the "OK" element, located at the bottom of the window.

6.

1. Ready! As a result, we received the result of counting in a predetermined cell. It took into account absolutely every filled cell.

7 Method 3: Using the account function

The account is ideal for working with numerical indicators. There are two common types of operator:

1. = Account (cell1; cell2; ... cell).
2. = Account (cell1: cell).

Step-by-step guide:

1. We produce a selection of the cell in which the counting will be displayed.
2. Moving in the subsection "Formulas". Click on the "Insert function" item. The screen displays a small window called "Function Insert". Reveal the list located next to the "Category" inscription. In the discontinued list, select the "statistical" element. In the "Select Function:" field, we find the account operator and click on it with LKM. After all manipulations, click on the "OK" element.

eight

1. The window of the arguments fill the coordinates of the necessary cells. After all manipulations, click on the "OK" element, located at the bottom of the window.

nine

1. Ready! As a result, we received the result of counting in a predetermined cell. Here are taken into account filled cells, with the exception of empty and those in which text information is located.

10 Method 4: Operator 8

This operator allows you to calculate the number of values ​​for specific conditions. General view of the operator: = 8 (Range; Criterion).

1. Range - This indicates the area in which it is necessary to calculate the number of coincidences on a specific condition.
2. The criterion is the condition for which coincidences will be distinguished.

We will analyze everything on a specific example. Step-by-step guide:

1. For example, we will determine the number of the word "running" in the column with sports. Moving in the field in which you are going to display the result.
2. Moving in the subsection "Formulas". Click on the "Insert function" item. The screen displays a small window called "Function Insert". Reveal the list located next to the inscription "Category:". In the discontinued list, select the "statistical" element. In the "Select Function:" field, we find the operator and click on it with LKM. After all manipulations, click on the "OK" element.

eleven

1. The screen appears the "function arguments" window. In the Range line, we introduce the coordinates of the cells participating in the counting. In the line "Criterion" we ask the condition itself. Drive here: "Run". After carrying out all manipulations, click on the "OK" element.

12

1. The operator calculated and displayed the number of coincidences in the highlighted field with the word "Run". We got sixteen coincidences.

13

Let's try editing the condition to better understand the work of the operator. Step-by-step guide:

1. Let's make counting other values ​​in this column, with the exception of the "Run" value.
2. We repeat the second point from the above instruction.
3. In the Range line, we enter the same addresses as in the above example. In the line "Criterion" we drive the symbol of inequality "" before the "Run" value. After all manipulations, click on "OK".

fourteen

1. As a result, we received a number of seventeen - the number of words in the selected column without taking into account the word "running".

At the end of the consideration of the method, we find out how the operator works with the conditions in which the characters ">" and "

1. Using the above instructions, insert the operator to check the necessary cell.
2. In the Range line, we enter the necessary coordinates of the cells of the speaker. In the "Criterion" line, enter the value "> 350". After all manipulations, click on "OK".

fifteen

1. As a result, we received a value of ten - the number of numbers is greater than 350 in the selected column.

16 Examples of using the function function in Excel

We will analyze another example for a more complete understanding of the operator's work. For example, we have the following marks with sales information:

17.

Purpose: Find out how much of goods sold is products from Samsung. We apply the following formula: = C3: C17; "samsung") / A17. As a result, we get a result of 40%:

18 Features of using the function of the function in Excel

Talk about some features of the operator:

• The register of signs does not matter if a line of a text species was set as a criterion;
• The result of the counting will be zero if the reference to the blank cell or an empty line will be introduced as a criterion;
• The operator can be applied as an array formula, in which case the number of cells with information corresponding to several conditions will occur.

Method 5: Using the operator is 8 operator

The operator is perisitely identical to the above-mentioned function. It differs in that it allows you to add a certain number of ranges with various or identical conditions. For example, we need to calculate the number of products sold in the amount of more than three hundred pieces. Additionally, it is necessary to calculate that the price of which product exceeds the value of six thousand rubles. Step-by-step guide:

1. We produce a selection of the cell in which the counting will be displayed.
2. Moving in the subsection "Formulas". Click "Insert a function." The screen displays a small window called "Function Insert". Reveal the list located next to the "Category" inscription. In the discontinued list, select the "statistical" element. In the "Select Function:" field, we find the operator signal and click on it with the LKM. After all manipulations, click on the "OK" element.

nineteen

1. The window familiar to us under the name "function arguments" appeared. Here, when entering one condition, a new line appears immediately to fill another condition. In the line "Range 1", we drive the address of the column in which the sales information is located in pieces. In the line "Condition 1", we drive the "> 300" indicator. In the line "Range 2", drive the address of the column in which the cost information is located. In the "Condition 2" line, add the value "> 6000". After all manipulations, click on "OK".

twenty

1. As a result, we obtained the result that shows which cells suitable for added conditions appeared in the specified range. We got the number of fourteen.

21 Method 6: Clamp function

The following option allows you to calculate the number of empty cells. To implement this procedure, it is perfectly suited. General view of the operator: = cleanposts (range). Step-by-step guide:

1. We produce a selection of the cell in which the counting will be displayed.
2. Moving in the subsection "Formulas". Click on the "Insert function" item. The screen displays a small window called "Function Insert". Reveal the list located next to the inscription "Category:". In the discontinued list, select the "statistical" element. In the "Select Function:" field, we find the operator to clean and click on it by the LKM. After all manipulations, click on the "OK" element.

22.

1. In the "Arguments of the function" appeared, we drive the necessary coordinates of the cells, and then click on the "OK" element.

23.

1. As a result, we obtained the result in which the number of empty cells will be displayed.

24.

Counting unique text values ​​in MS Excel

For example, consider the following sign:25.

Purpose: Calculate the number of unique text parameters in A7: A15. To do this, we use the formula: = Sumpacy ((A7: A15 "") / 8: A7: A15; A7: A15)).

How to find repetitive values ​​in Excel.

For example, we have the following plate:

26.

To calculate the recurring values, it is necessary to introduce the following formula in G5: = if (and $ 5: A $ 10; A5)> 1; 8) (A $ 5: A5; A5); 1). Then you need to copy this formula on the entire column.

Calculate the number of repetitive values ​​in the column

In order to calculate the required number of doubles in the column, you must make the following:

1. Use we will all the same sign from the above example. We produce the selection of column cells.
2. Mix in the formatting window.
3. In the "Select Term Type:" field, click on the element "use the formula to determine the formatable cells".
4. In the line "format the formula to determine the formatable cells", drive = 8 ($ A: $ A; A5)> 1.

27.

1. Ready! We have implemented the calculation of the same indicators in the column, and also allocated the recurring information in other colors.

Counting the number of a specific cell value in Excel provided

Consider this moment on a specific example. Suppose we have the following table plate:

28.

We allocate the E2: E5 range and introduce the formula: = 8: B3: B19; D2: D5). B3: B19 - cells containing estimates, and d2: D5 - cells, in which criteria are located for counting the number of correspondence. As a result, we get such a result:

29.

Conclusion

The EXEL table editor is a multifunctional program that allows you to solve a huge number of diverse tasks. The program provides several functions that allow you to calculate the number of values ​​in the specified range. Each user will be able to independently choose a more convenient method suitable for his tasks.

Message How to calculate the number of values ​​in the Excel column. 6 ways to count the number of values ​​in the Eksel column appeared first on information technology.