Good afternoon, dear reader!

I want to devote this article to repetitions of those values ​​that appear in your table, that is, we will learn how to count repetitions in Excel. This opportunity will be useful when calculating the same values ​​in the required range; it will help when you need more, for example, how many employees made checks, how many times they worked with a particular supplier and much more.

First, let's look at what columns with data look like, the repetitions of which we will actually count. For example, let's take a list of employees who make sales. Now you can count how many times an employee made sales, that is, we simply count how many repetitions of his last name are in the column. This can be done in several ways:

Using the COUNTIF function

In Excel, it is extremely easy to make such a calculation; just use it and it will do everything for you in a few seconds. In our case, the formula will be as follows:

=COUNTIF($B$2:$B$11,B15) In the first argument "range"$B$2:$B$11, indicate the one in which the duplicate data will be counted. Important! indicate random range data is not allowed. Its peculiarity is that it can only be a range of cells or a reference to a specific cell.

Second argument "criterion" We put an indication on the cell in which similar data will be calculated. If there is only one, you can enter it manually as a text word and instead of the cell address “B15” indicate “Nagaev A.V.”, the result will be similar, but only in one specific case, the possibility of automating the table will then be significantly reduced.

Additional information! In addition to directly indicating the search for data, the COUNTIF function can work with wildcard characters. There are two types of such signs: “?” and “*”, they can only be used when working with symbols. The “*” sign allows you to replace absolutely any number of values, and the “?” replaces only one character.

To work with numeric values, you must use comparison operator signs: “>”, “<», «<>" and "=". For example, to count numerical values ​​greater than “zero” enter “>0”, and to count non-empty cells you need to specify “<>».

Using the COUNTIFS function

When you need to count repetitions in Excel, but based on several criteria, then you need to work with the COUNTIFS function, which can easily do this.

In my example, I will add a sales category by city and use formulas to collect the repetitions I need:

=COUNTIFS($B$2:$B$11,B14,$C$2:$C$11,C14)
I note that the spelling of the function is absolutely similar to the previous COUNTIF function, the difference is only in their number. In our example there are two of them, but the function can work with 127 ranges.

Working with the LONG function

Now let's look at a situation where not everything is so simple and orderly, when information is compiled into one cell, for example, “Nagaev Gavrosh Karopachev Kozubenko Nagaev Gavrosh Kozubenko Nagaev Nagaev”. In this case, statistical functions will not help us; it is necessary to count the symbols and compare the repetitions of values ​​with the specified standard. For these purposes there are many other useful functions, using which this can be done quite simply:

=(LENGTH($B$2) -LENGTH(SUBSTITUTE($B$2,B5,"")))/LENGTH(B5)
So, using , we count how many characters are contained in the cell “$B$2” and “B5”, the result will be “71”. And then using the help we replace the current value with “empty”, we get the result “47”. The next step is to subtract our remainder “71-47=24” from the total number of characters and divide by the number of characters in one value “24/6=4”, as a result we get how many times in a line the required result occurs... Answer: 4. (This is result considering only the first search line).

Using VBA Functions

The last option considered is to count the number of repetitions using a function created in VBA. I did not write the functions, but simply offer you the option I found earlier to simplify your work.

First you need to run VBA and insert a new module using the commands "Insert" - "Module".
In the created module window you paste the function code:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) - Len(Replace(sTxt, sCntWord, ""))) / Len(sCntWord) End Function

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len (sTxt) - Len (Replace (sTxt, sCntWord, "")) / Len (sCntWord) End Function

After all this you call "Function Manager" in the control panel or using Ctrl+F3 and in the category "User Defined" you have a new necessary function.
We use the function on a cell in a standard way using the formula:

=GetRepeat ($B$2;B8), Where:

And that's all for me! I was very happy to help and share information about the ability to count repetitions in Excel. If you have anything to add to the article, write it in the comments. I look forward to your likes, this is the best incentive to see the benefits of my articles!

Our every day is a bank account, and the money in it is our time. There are no rich or poor here, everyone has 24 hours.
Christopher Rice

Let's say you want to find out how many unique values ​​there are in a range that contains duplicate values. For example, if the column includes the following:

The values ​​5, 6, 7, and 6 are output as three unique values: 5, 6, and 7.

The values ​​are "Kirill", "Sergey", "Sergey", "Sergey", the result is two unique values ​​- "Kirill" and "Sergey".

There are several ways to count unique values ​​among duplicates.

Counting the number of unique values ​​using a filter

Using the dialog box Advanced filter" you can extract unique values ​​from a column of data and insert them into a new location. Then use the function LINE you can count the number of elements in the new range.

Counting the number of unique values ​​using functions

To perform this task, use a combination of functions If, amounts, Frequency, SEARCH And DLST .

Assign a value of 1 to each of the true conditions using the function IF.

Add a total using a function amounts .

Count the number of unique values ​​using the function Frequency. Function FREQUENCY Does not handle text and null values. For the first occurrence of a particular value, this function returns a number equal to the number of occurrences of that value. For each occurrence of the same value that comes after the first one, this function returns zero.

Returns the location of a text value in a range using the function SEARCH. The return value is then used as an argument to the function FREQUENCY so that the corresponding text values ​​can be evaluated.

Finding empty cells using the function Len. The length of empty cells is 0.

Notes:

The formulas in this example must be entered as array formulas. If you have a current version of Office 365, you can simply enter the formula in the top left cell of the output range and press ENTER to confirm the use of the dynamic array formula. Otherwise, the formula must be entered using previous version array by selecting the output range, entering a formula in the top left cell of the range, and pressing the keys CTRL+SHIFT+ENTER for confirmation. Excel automatically inserts braces at the beginning and end of the formula. For more information about array formulas, see Using Array Formulas: Best Practices and Examples.

To view the step-by-step calculation of a function, select the cell containing the formula, and then on the tab formulas in the group Formula dependencies click the button Formula calculation.

Description of functions

Function Frequency Calculates the frequency of occurrence of values ​​in a range of values ​​and returns a vertical array of numbers. For example, use the function Frequency to count the number of test scores that fall within scoring ranges. Because this function returns an array, it must be entered as an array formula.

Function SEARCH searches for a specific element in a range of cells and returns the relative location of that element in the range. For example, if the range a1:A3 contains the values ​​5, 25, and 38, the formula function =Match(25;a1:A3;0) returns the number 2 because 25 is the second element of the range.

Function DLST returns the number of characters in a text string.

Function SUM calculates the sum of all numbers given as arguments. Each argument can be a range, cell reference, array, constant, formula, or the result of another function. For example, sum function (a1:A5) adds all the numbers contained in cells a1 to A5.

Function If returns one value if the specified condition is true, and another value if the specified condition is false.

More information

You can always ask a question to an Excel Tech Community specialist, ask for help in the Answers community, and also suggest new feature or improvement on the website

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

Method 1: Status Bar Pointer

The easiest way to solve the problem in the selected range is to look at the quantity in the status bar. To do this, simply select the desired range. It is important to take into account that the system considers each data cell as a separate unit. Therefore, to avoid double counting, since we need to find out the number of rows, we select only one column in the area under study. 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, it also happens when the table does not have completely filled columns, but each row has values. In this case, if we select only one column, then those elements that do not have 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 are empty in the selected column. In this case, 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 highlight filled cells in rows, but the quantity display 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". Now the number of selected rows will be displayed.

Method 2: Using a function

But, the above method does not allow recording the counting results in a specific area on the sheet. In addition, it provides the ability to count only those rows that contain values, and in some cases it is necessary to count all elements in total, including empty ones. In this case, the function will come to the rescue LINE. Its syntax looks like this:

StrOW(array)

It can be inserted 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, just press the button Enter.

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

For users who have little experience working with formulas in Excel, 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 the selected fragment. Each of these methods is appropriate to use for certain purposes. For example, if you need to record the result, then in this case the option with a function is suitable, and if the task is to count rows that meet a certain condition, then conditional formatting with subsequent filtering will come to the rescue.

SEO analysis of website text is a unique service that has no analogues. The ability to highlight “water”, spam and keys in the text allows you to make text analysis interactive and easy to understand.

SEO text analysis includes:

Character counter, counting the number of characters and words in text online

Using this online service, you can determine the number of words in the text, as well as the number of characters with and without spaces.

Determination of keys and semantic core of text

The ability to find search keys in a text and determine their number is useful both for writing new text and for optimizing an existing one. Arranging keywords by group and frequency will make navigation through the keys convenient and fast. The service will also find morphological variants of keys, which will be highlighted in the text when you click on the desired keyword.

Determining the percentage of water content of a text

This parameter displays the percentage of presence in the text of stop words, phraseological units, as well as verbal expressions, phrases, connecting words that are not significant and do not carry a semantic load. A small amount of “water” in the text is a natural indicator, while:

• up to 15%- natural content of “water” in the text;
• from 15% to 30%- excess “water” content in the text;
• from 30%- high content of “water” in the text.

Determining the percentage of text spam

The percentage of spammed text reflects the number of search keywords in the text. The more keywords in the text, the higher the spam level:

• up to 30%- absence or natural content of keywords in the text;
• from 30% to 60%- SEO-optimized text. In most cases search engines consider the given text to be relevant to the keywords indicated in the text.
• from 60%- highly optimized or keyword-spammed text.

Search for mixed words or words in different keyboard layouts

This parameter shows the number of words consisting of letters of different alphabets. Often these are Russian letters and English language, for example, the word “table”, where “o” is a letter of the English alphabet. Some copywriters replace some letters in Russian words with English ones in order to fraudulently increase the uniqueness of the text.

Example 1: Status Bar Data

The number of rows in the selected range is displayed in the status bar area. Select the desired range Excel values will automatically calculate the number of rows that will be displayed in the status bar. It must 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 must select one column and the bottom of the window will display the number of positions in the selected range.

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 look at the example below. Empty cells from the first column correspond to the values ​​of the cells in the second. Let’s select the first column in its entirety and, while pressing 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, but the quantity is not displayed, this means that you need to enable the appropriate option. To do this, right-click on the status bar and select “Quantity”.

Example 2: Calculation using formulas

The example discussed above does not make it possible to save the calculation results and display them in a cell on the sheet. Moreover, it is often necessary to take into account empty positions. To do this, it is convenient to use the LOTS formula.

The formula looks like: =String(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 taken into account. In addition, even if the selected area includes more than one column, the function will only count rows.

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

1) Place the cursor on the position where we will insert the formula, i.e. where the result will be displayed and click on the “Insert formula” icon.

2) The Formula Builder window will open. In the “Category” value, set “Links and arrays”, you can also select “Full alphabetical list”. Find the “CHROW” function and click “OK”

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

4) After performing the above steps at the specified position, Excel will automatically calculate the number of rows in the range. Calculations will be performed until you remove the formula from the cell.

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, formatting with a given condition is used, after which filters are applied.

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

2) Go to the main panel and select “Styles” then click on the “Conditional Formatting” icon. In conditional formatting, select “Rules for highlighting cells” and for our case, select the rule “Greater than...”

3) A dialog for setting conditions appears. On the left is a value greater than which, the cells will be colored with the specified color. On the right side the color is set. After selecting, click “OK”

4) As you can see below, positions that meet the conditions are colored in a given color. While on the main panel, select the entire range and click the “Sort and Filter” menu item and select “Filter”