Graphs and Charts (5)
Working with VB project (12)
Conditional Formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (64)
Miscellaneous (41)
Excel bugs and glitches (4)

How to count the number of repetitions

In this article I want to tell you how you can count the number of repetitions of a value in a table or cell. Let's start in order. There is a table:

And it is necessary to count the number of repetitions of each name:

Oddly enough, this is quite easy to do: Excel has a function - COUNTIF, with which you can do all this literally in seconds. If the number of repetitions of each name needs to be displayed in column B of the table, and the names themselves are located in column A:
=COUNTIF($A$2:$A$30, A2)
Range($A$2:$A$30) - indicate the range cells in which the values ​​are written, the number of which needs to be counted. main feature: This argument can only be a reference to a cell or range of cells. It is not acceptable to specify an arbitrary array of values.
Criterion(A2) - indicates a reference to a cell or directly the value to be calculated. Those. You can also specify it like this: =COUNTIF($A$2:$A$30 ;"Apple") . In addition, you can use wildcard characters: ? And *. Those. By specifying “*banana*” as the Criterion, you can count the number of cells in which the word “banana” appears (banana, banana, bananas, banana juice, one hundred bananas, three bananas and a nut, etc.). And by specifying “banana*” - meanings starting with “banana” (bananas, banana juice, banana grove, etc.). "?" - replaces only one character, i.e. By specifying “ban?n” you can count rows with the value “banana” and with the value “banon”, etc. If you specify =COUNTIF($A$2:$A$30 ;"*") as the criterion, then all text values ​​will be counted. Numeric values ​​are ignored. These wildcard characters (* and?) cannot be applied to numeric values ​​- only to text. Those. if you specify “12*” as a criterion, then the numbers are 1234, 123, 120, etc. will not be counted. To count numeric values, use comparison operators: =COUNTIF($A$2:$A$30 ;">12")

As can be seen from the second figure, the names are not repeated there, although in the table they are all written mixed together. I will not focus on this - I already described this in the article How to get a list of unique (non-repeating) values? and if necessary, you can use any method described in it.

If you need to count the number of repetitions based on several conditions (values), then starting from Excel 2007 this can easily be done using the COUNTIFS function. The syntax of the function is almost the same as COUNTIF, only there are more conditions and ranges:
=COUNTIFS($A$2:$A$30, A2, $B$2:$B$30, B2)
it is assumed that the conditions are written in column B
Essentially it's just a list:
=COUNTIFS(Condition_Range1, Condition1, Condition_Range2, Condition2, Condition_Range3, Condition3, etc.)

But. There are cases when the list is not located in the table at all, but in one cell ($D$1):
Melon Kiwi Pear Apple Melon Pear Pear Watermelon Apple Banana Apple Apple Banana Apple Apple Melon Melon Kiwi Banana Melon Watermelon Melon Kiwi Apple Melon Pear Apple Kiwi Watermelon
Here COUNTIF definitely won’t help. But Excel is full of other functions and everything can be done just as simply:
=(LENGTH($D$1)-LENGTH(SUBSTITUTE($D$1 ; D3 ;"")))/LENGTH(D3)
DLST- counts the number of characters in the specified cell/row($D$1 , D3)
SUBSTITUTE(text; old_text; new_text) - replaces the specified character in the specified text with any other set value. By default, replaces all repetitions of the specified character. This is exactly what the algorithm is based on. Using the value Banana(D3) as an example, a step-by-step analysis of the formula:

• using the LENGTH function, we get the number of characters in the line with the source text($D$1) =(170-LENGTH(SUBSTITUTE($D$1 ; D3 ;"")))/LENGTH(D3) ;
• Using the SUBSTITUTE function, we replace all values ​​of Banana(D3) in the line with the source text($D$1) with empty ones and using LENGTH we get the number of characters of the line after this replacement =(170-155)/LENGTH(D3) ;
• subtract the number of characters in the line after replacement from the total number of characters and divide the result by the number of characters in the criterion =(170-155)/5.

We get the number 3. Which is what we needed.

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

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, click right click mouse 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 main panel and select “Styles” then click on the “Conditional Formatting” icon. IN conditional formatting select “Rules for selecting cells” and for our case, select the rule “Greater...”

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”

You may need to count the number of cells in Excel in various cases. In this article, we will look at how to count blocks with certain values, empty ones and if they fall under specified conditions. For this we will use following functions: COUNT, COUNTIF, COUNTIFS, COUNTBLANK.

Completed

First, let's look at the simplest method. If you need to count the number of filled blocks in Excel, select a range of cells in the document - there are various ways, read about them by following the link - and in "Status Bar" pay attention to the item “Quantity”. The number of non-empty blocks will be written there: all with text and numbers are taken into account, but empty ones are not.

If you need to count blocks in a table filled with certain values ​​and use this number in formulas for calculations, then this method will not work, since the data in the table may change periodically. Therefore, let's move on to considering functions.

Where are the numbers entered?

COUNT function – counts blocks filled only with numeric values. Select H1, put “=”, write the function “COUNT”. Specify the desired range as the function argument (F1:G10). If there are several ranges, separate them with ";" – (F1:G10;B3:C8) .

A total of 20 blocks are filled. The one containing the text was not counted, but those filled with date and time were counted.

With specific text or meaning

For example, there is a table that shows how many kilograms of a certain product were sold per day. Let's calculate how many products were sold weighing more than 5 kilograms. To do this, you need to count how many blocks are in the Weight column, where the value is greater than five. The function will look like this: =COUNTIF(B2:B13,”>5″). It will calculate the number of blocks containing more than five contents.

In order to extend the function to other blocks, and, say, change the conditions, you need to fix the selected range. You can do this using absolute links in Excel.

– number of cells with negative values: =COUNTIF(B2:B13;"<0″) ;
– the number of blocks whose content is greater (less) than in A10 (for example): =COUNTIF(B2:B13,">"&A10);
– cells whose value is greater than 0: =COUNTIF(B2:B13,”>0″);
– non-empty blocks from the allocated range: =COUNTIF(B2:B13;"<>») .

You can also use the COUNTIF function to calculate cells in Excel that contain text. For example, let's calculate how many fruits are in the table. Let’s select an area and specify “fruit” as a criterion. All blocks containing the given word will be counted. You can not write text, but simply select the rectangle that contains it, for example C2.

For the COUNTIF formula, case does not matter; cells containing the text “Fruit” and “fruit” will be counted.

You can also use special characters as a criterion: “*” and “?” . They apply only to text.

Let's count how many products begin with the letter A: “A*”. If you specify “apricot*”, then all products that begin with “apricot” will be taken into account: apricot juice, apricot jam, apricot pie.

The symbol "?" You can replace any letter in a word. By writing “f?ukt” in the criteria, the words fruit, fuukt, fuukt will be taken into account.

To count words in cells that consist of a certain number of letters, put question marks in a row. To count products that have 5 letters in their names, we set “?????” as the criterion. .

If you set an asterisk as a criterion, all blocks containing text from the selected range will be counted.

With multiple criteria

The COUNTIFS function is used when you need to specify several conditions, the maximum number of them in Excel is 126. As an argument: we set the first range of values, and indicate the condition, separated by “;” we set the second range and write a condition for it - =COUNTIFS(B2:B13,">5″,C2:C13,"fruit").

In the first range, we specified that the weight be more than five kilograms; in the second, we chose that it should be fruit.

Empty blocks

Calculating the number of cells that contain text or numeric values ​​in Excel is not that difficult. Use special functions for this and set conditions. With their help, you can count both empty blocks and those where certain words or letters are written.

Rate this article:

(1 ratings, average: 5,00 out of 5)

Webmaster. Higher education with a degree in Information Security. Author of most articles and computer literacy lessons

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 specified in the text.
• from 60%- highly optimized or spammed keywords 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 in English, 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.