How to count the number of values ​​in an Excel column. 6 ways to count the number of values ​​in an Excel column

Often, users of a spreadsheet editor are faced with a question that concerns the calculation of the number of indicators in a column. In other words, it is necessary to count the number of fields in a column filled with specific textual or numerical information. The spreadsheet editor has many variations that allow you to perform this function.

Procedure for counting values ​​in a column

Depending on the goals of the user in the table editor, you can implement the calculation of all data in a column. Let’s analyze each method in more detail.

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

This method is the easiest to use. It implements the calculation of numeric and textual information, but cannot be used when working with conditions. General algorithm of actions:

1. Select the required data area.
2. Look at the status bar.
3. Let’s find out the number of values.

It is worth noting! When counting, all cells are taken into account, except for empty ones.

The method is convenient to use only when working with average data volumes, otherwise you need to use other options. Another disadvantage of the method is that the result is visible only at the moment of selection, so the output values ​​must either be remembered or fixed elsewhere. Step-by-step guide to enable the Quantity measure:

1. Right click on the status bar.
2. A list appears on the screen. Here you need to check if there is a checkmark next to the “Quantity” parameter. If it is not there, then it must be set by clicking on the left mouse button.

3. Ready! Now the indicator “Quantity” is located in the status line of the spreadsheet processor.

Method 2: Using the COUNTA Function

This operator implements the same as the previous method, but additionally fixes the result. Other disadvantages remain, as the COUNTA function cannot work with conditions.

COUNTA implements the count of each filled cell in the selected range, with the exception of empty cells.

There are two general kinds of operator:

1. =COUNTA(cell1,cell2,…cellN).
2. =COUNTA(cell1:cellN).

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

1. We select the cell in which the count will be displayed as a result.
2. We move to the “Formulas” subsection. Click “Insert Function”.

3. An alternative option is to click on the “Insert Function” icon, which is located next to the line for a set of formulas.

4. A small “Insert function” window appeared on the screen. We open the list located next to the inscription “Category”. In the drop-down list, select the item “Statistical”. In the field “Select a function:” we find the COUNTA operator and click on it with LMB. After carrying out all the manipulations, click on the “OK” element.

5. The display shows a window for writing operator arguments. Here you need to specify arguments by listing cells or entering a range. It is worth noting that the range can be set along with the name. After carrying out all the manipulations, click on the “OK” element, located at the bottom of the window.

6. Ready! As a result, we got the result of the calculation in a pre-selected cell. Here, absolutely every filled cell was taken into account.

Method 3: Using the COUNT function

COUNT is ideal for working with numbers. There are two general kinds of operator:

1. =COUNT(cell1,cell2,…cellN).
2. =COUNT(cell1:cellN).

Walkthrough:

1. We select the cell in which the count will be displayed as a result.
2. We move to the “Formulas” subsection. Click on the “Insert function” element. A small window called “Insert Function” was displayed on the screen. We open the list located next to the inscription “Category”. In the drop-down list, select the item “Statistical”. In the field “Select a function:” we find the operator ACCOUNT and click on it LMB. After carrying out all the manipulations, click on the “OK” element.

3. The argument window is filled with the coordinates of the required cells. After carrying out all the manipulations, click on the “OK” element, located at the bottom of the window.

4. Ready! As a result, we got the result of the calculation in a pre-selected cell. Here, filled cells are taken into account, with the exception of empty cells and those in which text information is located.

Method 4: COUNTIF Operator

This operator allows you to count the number of values ​​for specific conditions. General view of the operator: =COUNTIF(range, criteria).

1. Range – here you specify the area in which you want to count the number of matches for a specific condition.
2. Criteria – this is the condition by which matches will be searched.

Let’s take a look at a specific example. Walkthrough:

1. For example, let’s determine the number of the word “running” in the column with sports. We move to the field in which we are going to display the result.
2. We move to the “Formulas” subsection. Click on the “Insert function” element. A small window called “Insert Function” was displayed on the screen. Expand the list next to the inscription “Category:”. In the drop-down list, select the item “Statistical”. In the “Select a function:” field, find the COUNTIF operator and click on it with LMB. After carrying out all the manipulations, click on the “OK” element.

3. The Function Arguments window appears on the screen. In the line “Range” enter the coordinates of the cells involved in the calculation. In the line “Criterion” we set the condition itself. We drive in here: “run”. After carrying out all the manipulations, click on the element “OK”.

4. The operator counted and displayed in the pre-selected field the number of matches with the word “running”. We got sixteen matches.

Let’s try to edit the condition in order to better understand the work of the operator. Walkthrough:

1. Let’s make it so that the other values ​​in this column are counted, with the exception of the value “running”.
2. We repeat the second point from the above instructions.
3. In the line “Range” we enter the same addresses as in the above example. In the line “Criterion” we drive in the inequality symbol “<>” before the value “running”. After carrying out all the manipulations, click on “OK”.

4. As a result, we got the number seventeen – the number of words in the selected column, excluding the word “run”.

At the end of the consideration of the method, let’s find out how the operator works with conditions that use the symbols “>” and “<". For example, let's determine how many times a value greater than 350 appears in the Sold column. Walkthrough:

1. Using the instructions above, insert the COUNTIF operator into the desired cell.
2. In the line “Range” enter the necessary coordinates of the cells of the column. In the line “Criterion” enter the value “> 350”. After carrying out all the manipulations, click on “OK”.

3. As a result, we got the value ten – the number of numbers greater than 350 in the selected column.

Examples of using the COUNTIF function in Excel

Let’s look at another example for a more complete understanding of the work of the COUNTIF operator. For example, we have the following table with sales information:

Purpose: to find out how many of the goods sold are products from Samsung. We apply the following formula: =COUNT(C3:C17;»Samsung»)/A17. As a result, we get a result of 40%:

Features of using the COUNTIF function in Excel

Let’s talk about some features of the operator:

• case of characters does not matter if a string of text type was specified as a criterion;
• the result of the calculation will be zero if a reference to an empty cell or an empty line is entered 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 at once will be counted.

Method 5: Using the COUNTIFS Operator

The COUNTIFS operator is almost identical to the above function. It differs in that it allows you to add a number of ranges with different or the same conditions. For example, we need to count the number of products sold in quantities of more than three hundred pieces. Additionally, you need to calculate the price of which product exceeds the value of six thousand rubles. Walkthrough:

1. We select the cell in which the count will be displayed as a result.
2. We move to the “Formulas” subsection. Click “Insert Function”. A small window called “Insert Function” was displayed on the screen. We open the list located next to the inscription “Category”. In the drop-down list, select the item “Statistical”. In the “Select a function:” field, find the COUNTIFS operator and click on it with LMB. After carrying out all the manipulations, click on the “OK” element.

3. The familiar window called “Function Arguments” was displayed. Here, when you enter one condition, a new line immediately appears to fill in another condition. In the line “Range 1” we drive in the address of the column in which the information on sales in pieces is located. In the line “Condition 1” we drive in the indicator “> 300”. In the line “Range 2” we drive in the address of the column in which information about the cost is located. In the line “Condition 2” add the value “> 6000”. After carrying out all the manipulations, click on “OK”.

4. As a result, we got a result that shows how many times cells appeared in the specified range that matched the added conditions. We got the number fourteen.

Method 6: COUNTBLANK function

The next option allows you to count the number of empty cells. COUNTBLANK is great for implementing this procedure. General view of the operator: =COUNTBLANK(range). Walkthrough:

1. We select the cell in which the count will be displayed as a result.
2. We move to the “Formulas” subsection. Click on the “Insert function” element. A small window called “Insert Function” was displayed on the screen. Expand the list next to the inscription “Category:”. In the drop-down list, select the item “Statistical”. In the field “Select a function:” we find the operator COUNTEMPTY and click on it LMB. After carrying out all the manipulations, click on the “OK” element.

3. In the “Function Arguments” window that appears, we drive in the necessary cell coordinates, and then click on the “OK” element.

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

Counting Unique TEXT Values ​​in MS EXCEL

For example, consider the following table:

Purpose: to count the number of unique text indicators in A7:A15. To do this, we use the formula: =СУММПРОИЗВ((А7:А15<>»»)/СЧЕТЕСЛИ(А7:А15;А7:А15)).

If there are also numerical indicators in the specified coordinates, then they will also be calculated. In order to not count numbers, you must use: =СУММПРОИЗВ(ЕТЕКСТ(А7:А15)/СЧЕТЕСЛИ(А7:А15;А7:А15)).

How to find duplicate values ​​in Excel.

For example, we have the following label:

To count duplicate values, enter the following formula in G5: =ЕСЛИ(СЧЕТЕСЛИ(А$5:А$10;А5)>1;СЧЕТЕСЛИ(А$5:А5;А5);1). Then you need to copy this formula to the entire column.

Count the number of duplicate values ​​in a column

In order to calculate the required number of duplicates in a column, you must do the following:

1. We will use the same plate from the above example. We select the cells of the column.
2. Move to the formatting window.
3. In the “Select the type of rule:” field, click on the item “Use a formula to determine the formatted cells.”
4. In the line “Format formula for determining formatted cells” we drive =COUNTIF($A:$A,A5)>1.

5. Ready! We implemented the calculation of the same indicators in a column, and also highlighted the repetitive information in a different color.

Counting number of specific cell value in Excel on condition

Let’s consider this point on a specific example. Let’s say we have the following label to work with:

Select the range E2:E5 and enter the formula: =СЧЕТЕСЛИ(B3:B19;D2:D5). B3:B19 are cells containing scores, and D2:D5 are cells that contain criteria for counting the number of matches. As a result, we get this result:

Conclusion

The spreadsheet editor Excel is a multifunctional program that allows you to solve a huge number of different tasks. The program provides several functions that allow you to count the number of values ​​in a given range. Each user will be able to independently choose for himself a more convenient method suitable for his tasks.