How to count the number of numbers. Counting cells in excel using count and count if functions. Count numbers and values

Count the number of cells in Excel may be required in various cases. In this article, we will look at how to count blocks with certain values, empty and if they meet the specified conditions. We will use the following functions for this: COUNT, COUNTIF, COUNTIFS, COUNTBLANK.

Completed

First, let's look at the easiest way. If you need to count the number of filled blocks in Excel, select a range of cells in the document - there are different ways, read about them by clicking on the link - and in "Status bar" pay attention to the item "Quantity". The number of non-empty blocks will be written there: everything with text and numbers is taken into account, and 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 way will not work, since the data in the table may change periodically. Therefore, let's move on to considering functions.

Where numbers are entered

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

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

With a specific text or meaning

For example, there is a table that indicates how many kilograms of a particular product were sold per day. Let's calculate how many goods were sold weighing more than 5 kilograms. To do this, you need to calculate how many blocks are in the Weight column, where the value is greater than five. The function will look like in the following way: = COUNTIF (B2: B13; "> 5")... It will calculate the number of blocks with more than five content.

In order to stretch the function to other blocks, and, say, change the conditions, it is necessary 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, the content of which is more (less) than in A10 (for example): = COUNTIF (B2: B13; ">" & A10);
- cells with a value greater than 0: = COUNTIF (B2: B13; "> 0");
- non-empty blocks from the selected 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. Select the area and indicate "fruit" as a criterion. All blocks with the given word will be counted. You can not write the 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 as a criterion Special symbols: "*" and "?" ... They only apply to text.

Let's count how many products start with the letter A: "A *". If you indicate "apricot *", then all goods that start with "apricot" are taken into account: apricot juice, apricot jam, apricot pie.

The symbol "?" you can replace any letter in a word. Writing in the criterion "f? Ukt" - the words fruit, fuukt, fyukt 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 goods with 5 letters in the name, we will put as a criterion "?????" ...

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

With multiple criteria

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

In the first range, we indicated that the weight was more than five kilograms, in the second, we chose that it was fruit.

Empty blocks

It is not so difficult to count the number of cells in Excel that contain text or numeric values. 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 article:

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

Webmaster. Higher education in the specialty "Information Security" .. Author of most articles and lessons of computer literacy

Example 1: data from the status bar

The number of lines in the selected range is displayed in the status bar area. Highlight the desired range Excel values will automatically calculate the number of lines that will be displayed on the status bar. It should 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 need to select one column and the number of positions in the selected range will be displayed at the bottom of the window.

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 take a look at the example below. Empty cells from the first column correspond to the values ​​of the cells of the second. Select the entire first column and, while holding down 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, and the quantity is not displayed, then this means that you need to enable the corresponding option. To do this, click right click mouse on the status bar and select "Quantity".

Example 2: Calculation Using Formulas

The above example does not provide the ability to save the counting results and display them in a cell on a worksheet. Moreover, it is often necessary to take into account empty positions as well. For this, it is convenient to use the ROWS formula.

The formula is: = ROWS (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 counted. In addition, even if an area is selected that includes more than one column, the function will count only rows.

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

1) Set the cursor to the position where we will insert the formula, that is, where the result will be displayed and click on the "Insert formula" icon.

2) The Formula Builder window will open. Set the "Category" value to "References and Arrays", you can also select "Complete alphabetical list". Find the function "ROWS" and click "OK"

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

4) After doing the above steps at the specified position, Excel will automatically calculate the number of rows in the range. The calculations will continue until you remove the formula from the cell yourself.

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, conditional formatting is used and then filters are applied.

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

2) Go to the main panel and select "Styles" then click on the "Conditional Formatting" icon. In conditional formatting, select the "Cell Selection Rules" and for our case, select the "Greater ..." rule.

3) A dialog for specifying conditions appears. On the left, a value is indicated above which, the cells will be colored with the specified color. The color on the right of the part is set. After selection, click "OK"

4) As you can see below, the items that match the conditions are colored with the specified color. While on the main panel, select the entire range and click the "Sort and Filter" menu item and select "Filter"

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

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

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

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

Counting the number of unique values ​​using a filter

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

Counting the number of unique values ​​using functions

Use a combination of functions to accomplish this task If, sums, Frequency, SEARCH and DLSTR .

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

    Add the total using the function sums .

    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 specified value, this function returns a number equal to the number of occurrences of that value. For each occurrence of the same value after the first, this function returns zero.

    Returns the location of a text value in a range using a 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 a function Len... The length of empty cells is 0.


Notes:

    The formulas shown in this example must be entered as array formulas. If you have the current version of Office 365 installed, you can simply enter a formula in the upper 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 the previous version of the array by selecting the output range, entering the formula in the upper left cell of the range, and pressing CTRL + SHIFT + ENTER to confirm. Excel automatically inserts curly braces at the beginning and end of the formula. For more information on array formulas, see the article Using Array Formulas: Best Practices and Examples.

    To view the Step-by-Step calculation of a function, select the cell with the formula, and then on the tab formulas in a group Formula dependencies press the button Calculation of a formula.

Description of functions

    Function Frequency Computes 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 results that fall within the grade ranges. Since this function returns an array, it must be entered as an array formula.

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

    Function DLSTR 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 up all the numbers contained in cells A1 through A5.

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

additional information

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


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 calculate 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 in a cell. Let's start in order. There is a table:

And you need to count the number of repetitions of each name:

Oddly enough, but it is quite simple to do this: Excel has a function - COUNTIF, with the help of which all this can be done 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) - the cells of the range are indicated in which the values ​​are written, the number of which must be counted. main feature: This argument can only be a reference to a cell or a range of cells. It is not allowed to specify an arbitrary array of values.
Criterion(A2) - a reference to a cell or directly a value for counting is indicated. Those. you can also specify it like this: = COUNTIF ($ A $ 2: $ A $ 30; "Apple"). In addition, you can use wildcards:? and *. Those. specifying "* banana *" as the Criterion, you can count the number of cells in which the word "banana" occurs (banana, banana, bananas, banana juice, one hundred bananas, three bananas and a nut, etc.). And specifying "banana *" - values ​​starting with "banana" (bananas, banana juice, banana grove, etc.). "?" - replaces only one character, i.e. specifying "ban? n" you can count lines with the value "banana" and with the value "banon", etc. If you specify = COUNTIF ($ A $ 2: $ A $ 30; "*") as a criterion, then all text values ​​will be counted. Numerical values ​​are ignored in this case. These wildcards (* and?) Cannot be applied to numeric values ​​- only to text. Those. if you specify "12 *" as a criterion, then the numbers 1234, 123, 120, etc. will not be counted. Comparison operators should be used to count numeric values: = COUNTIF ($ A $ 2: $ A $ 30; "> 12")

As you can see from the second figure, the names are not repeated there, although they are all mixed in the table. I will not dwell 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 with Excel 2007 this can be easily done using the COUNTIFS function. The syntax of the function is almost the same as that of 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
In fact, it is just an enumeration:
= COUNTIFS (Condition_range1; Condition1; Condition_range2; Condition2; Condition_range3; Condition3; etc.)

But. There are times when the list is not located in a 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
COUNTIF will definitely not help here. But Excel is full of other functions and everything can be done just as easily:
= (DLSTR ($ D $ 1) -LSTR (SUBSTITUTE ($ D $ 1; D3; ""))) / DLSTR (D3)
DLSTR- counts the number of characters in the specified cell / line ($ D $ 1, D3)
SUBSTITUTE(text; old_text; new_text) - replaces the specified character in the specified text with any other specified value. By default, replaces all repetitions of the specified character. This is what the algorithm is based on. Using the value Banana (D3) as an example, step-by-step analysis of the formula:

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

We get the number 3. That 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: a lpha (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))

Very often, when working in Excel, you need to count the number of cells in a worksheet. These can be empty or filled cells containing only numeric values, and in some cases, their content must meet certain criteria. In this tutorial, we will go into detail about two main Excel functions to count data - CHECK and COUNTIF, and also get to know the less popular - ACCOUNT, COUNTBLANK and COUNTIFS.

CHECK()

Statistical function CHECK counts the number of cells in the argument list that contain only numeric values. For example, in the figure below, we have counted the number of cells in a range that consists entirely of numbers:

In the following example, two cells in a range contain text. As you can see, the function CHECK ignores them.

But cells containing date and time values ​​are taken into account:

Function CHECK can count the number of cells in several non-contiguous ranges at once:

If you need to count the number of nonblank cells in a range, you can use the statistical function ACCOUNT... Cells containing text, numeric values, date, time, and logical values ​​TRUE or FALSE are considered non-empty.

COUNTIF ()

Statistical function COUNTIF allows you to count the cells of the worker Excel worksheet using of various kinds conditions. For example, the following formula returns the number of cells that contain negative values:

The following formula returns the number of cells with a value greater than the contents of cell A4.

COUNTIF allows you to count cells containing text values. For example, the following formula returns the number of cells with the word “text,” and is not case-sensitive.

Boolean function condition COUNTIF may contain wildcards: * (asterisk) and ? (question mark). An asterisk stands for any number of arbitrary characters, and a question mark stands for one arbitrary character.

Function COUNTIF even allows you to use formulas as a condition. For example, to count the number of cells with values ​​greater than the average, you can use the following formula:

If one condition is not enough for you, you can always use the statistical function COUNTIFS. This function allows you to count cells in Excel that satisfy two or more conditions at once.

For example, the following formula counts cells with values ​​greater than zero but less than 50:

Function COUNTIFS allows you to count cells using the condition AND... If you want to calculate the quantity with the condition OR, you need to use several functions COUNTIF... For example, the following formula counts cells that start with the letter A or with the letter TO:

Excel's functions for counting data are very useful and can come in handy in almost any situation. I hope that this tutorial has revealed all the secrets of functions for you. CHECK and COUNTIF, as well as their closest associates - ACCOUNT, COUNTBLANK and COUNTIFS... Come back to us often. All the best and success in learning Excel.