Suppose you want to determine how many times a particular text or number appears in a range of cells. For instance:

If a range (for example, a2: D20) has numeric values ​​5, 6, 7, and 6, then the number 6 occurs two values.

If the column has the values ​​"Baturin", "Belov", "Belov" and "Belov", then "Belova" has three values.

Counting the number of occurrences of a single value using the COUNTIF function

Use the function COUNTIF to find out how many times a specific value occurs in a range of cells.

For more information, see the article COUNTIF function.

Counting the number of occurrences based on multiple criteria using the COUNTIFS function

Function COUNTIFS same function COUNTIF with one important exception: COUNTIFS allows you to apply criteria to cells in multiple ranges and counts the number of matches for each criterion. Up to 127 pairs of ranges and criteria can be used with the COUNTIFS function.

The syntax for the COUNTIFS function is as follows:

COUNTIFS(condition_range1; condition1; [condition_range2; condition2]; ...)

See example below.

For more information on using this function to count occurrences in multiple ranges and with multiple conditions, see the article COUNTIFS function.

Counting the number of occurrences based on conditions using the COUNT and IF functions

Let's say you need to determine how many sellers sold a specific item in a specific region, or you want to know how many sales processed with a specific value made by a specific seller. You can use functions If and check together. that is, first the function is used If to check the condition, and then, if the function's result if true, the function is used to count the cells check .

Notes:

For more information on these functions, see the COUNT function and IF function.

Counting the number of occurrences of multiple text and numeric values ​​using the SUM and IF functions

In the following examples, the functions IF and SUM used together. Function IF first checks the values ​​in specific cells, and then if it returns TRUE, the function SUM adds the values ​​that meet the condition.

Notes: The formulas shown in this example must be entered as array formulas.

Example 1

The function above means that if the range C2: C7 contains values Shashkov and Tumanov, then the SUM function should display the sum of the records in which the condition is met. The formula will find in the given range three records for "Checkers" and one for "Mists" and display 4 .

Example 2

The function above means that if cell D2: D7 contains values ​​less than $ 9,000 or more than $ 19,000, then the SUM function should display the sum of all records in which the condition is satisfied. The formula finds two records D3 and D5 with values ​​less than $ 9,000, and then D4 and D6 with values ​​greater than $ 19,000 and displays 4 .

Example 3

The above function says that D2: D7 contains accounts for Baturin for less than $ 9000, and the amount should display the amount of records in which it is respected. The formula will find cell C6 that matches the condition and display 1 .

Counting the number of occurrences of multiple values ​​using a pivot table

You can use the PivotTable to display totals and count the number of repetitions in a PivotTable. A pivot table is an interactive way to quickly summarize large amounts of data. You can use it to expand and collapse the presentation tiers to get accurate results and drill down to the bottom line on topics of interest. In addition, you can move rows to columns or columns to rows ("roll up" them) to view the number of occurrences of a value in the PivotTable. Consider an example of a Sales spreadsheet that calculates the number of sales values ​​for the Golf and Tennis sections for specific quarters.

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 Excel User Voice website.

Note: This page has been automatically translated and may contain inaccuracies and grammatical errors. It is important for us that this article is useful to you. Was the information helpful? For convenience also

Hello friends. Today we will learn how to count cells in Excel. These functions solve wide range tasks for representatives of many professions. By performing intermediate calculations, they become the basis for automating your calculations. I know many managers who use the counting functions to manage their impressive product line.

If you just need to know the number of values ​​without using it in calculations, it is convenient to look at the data in the status bar:

Number of values ​​in the status bar

You can select indicators displayed in a row by right-clicking on it.

Status bar customization

If you need to use the number of values ​​in further calculations, use the functions described below. For the convenience of notation, we will assume that the data array for which the count is kept is. In your formulas, you can use the data range you want instead of the name.

How to count the number of cells in Excel

There are two functions for counting the number of cells in Excel:

1. ROWS (array) - counts the number of rows in the selected range, regardless of what its cells are filled with. The formula gives the result only for a rectangular array of adjacent cells, otherwise;

We count the number of lines

1. NUMBERCOLUMN (array)- similar to the previous one, but counts the number of array columns

Excel does not have a function to determine the number of cells in an array, but this can be easily calculated by multiplying the number of rows by the number of columns: = ROWS (array) * ROWS (array).

How to count blank cells in Excel

Counting empty cells

The function considers a cell to be empty if nothing is written in it, or the formula inside it returns an empty string.

How to count the number of values ​​and numbers in Excel

We count numerical values

If you need to determine the number of cells containing values, we use the function COUNT (value1; value2; ...)... Unlike the previous function, it will count not only numbers, but also any combination of characters. If the cell is not empty, it will be counted. If a cell contains a formula that returns zero or an empty string, the function will also include it in its result.

Count nonblank cells

How to count cells with a condition in Microsoft Excel

• Array - the range of cells among which the count is made. You can only specify a rectangular range of adjacent cells;
• Criterion - the condition by which the selection takes place. Write text and numeric conditions with comparison signs in quotation marks. We write the equality to the number without quotes. For instance:
  • "> 0" - count cells with numbers greater than zero
  • "Excel" - count the cells in which the word "Excel" is written
  • 12 - counting cells with the number 12

Cell count with condition

If you need to take into account several conditions, use the function COUNTIFS (array1; criterion1; [array2]; [criterion1] ...)... The function can contain up to 127 array-criterion pairs.

If you use different arrays in one such function, they must all contain the same number of rows and columns.

To find the number that most often occurs in an array, there is a function in Excel FASHION (number1; number2; ...)... The result of its execution will be the same number that occurs most often. To determine their number, you can use a combination of summation formulas and array formulas.

If there are several such numbers, the one that appears earlier in the list will be displayed. The function works only with numeric data.

Frequent number

Here, perhaps, is the entire list of functions that I want to present to you within the framework of this post. There are enough of them to solve the most popular cell counting problems. Combine them with other functions (for example) for maximum results.

In the next article we will explore. Stop by to read, even if you are sure that you know everything about it. I think there will be something just for you!

On the world wide web, there are many different programs for counting the number of characters in a given text. These can be both small, specially designed programs for this purpose, and various text editors.

The most popular way for many users to determine the number of characters in a text has been and remains a time-tested text editor Microsoft Word.

Determining the number of characters for a whole text

You need to put the cursor at the beginning of the text, more precisely, before the first character of the text. Then you should find and use the Statistics tool in Word editor, which is the best suited for such calculations.

Looking for Statistics inWord 2007

1) Place the text in the Word 2007 editor.

2) Place the cursor at the beginning of the text.

3) Find the bottom line in the Word editor, which is called the status bar (number 2 in Fig. 1):

4) Click on the "Number of words" option (number 1 in Fig. 1), after which the "Statistics" window appears. Here we see that the number of characters (without spaces) is 2.304, and with spaces - 2.651.

By the way, if someone tells you that you need, for example, 2000 characters, without specifying, with or without spaces, then this means “with spaces”.

All this is good. But what if there is a status bar in Word 2007, but it does not have the "Number of words" option?

In this case, click on an empty space in the RMB status bar (with the right mouse button), for example, in the place where the number 2 is in Fig. 1. The "Status bar settings" window will appear (Fig. 2):

Rice. 2. Check the presence of a check mark opposite the "Number of words" option in the status bar of Word.

Here you should put a tick in front of the "Number of words" option (number 2 in Fig. 2). Then it will automatically appear in the status bar of Word.

Statistics in Word 2010

Everything here exactly coincides with what is written above about Word 2007.

Looking for Statistics in Word 2003

With the left mouse button, you need to make a single click on the "Service" function located in the upper pane of Word (Fig. 3).

In the window that opens, select the sub-item "statistics", after a single click on which in the center of the text editor Word appears a small window "Statistics" with all the necessary statistical information about the text. The number of pages, words, characters (without spaces and with spaces), paragraphs and lines is determined:

Counting characters in a piece of text

It is often necessary to calculate the number of characters for a particular part of the text. It's not hard to do this in Word:

1) you just need to select the desired part of the text to count the number of characters and

2) find the "Statistics" window (that is, perform the same manipulations as described above for the whole text).

What is a Sign Reader?

There are so-called "sign readers" on the network - services designed to count the number of characters in a text online. Online services are convenient in that you do not need to install any programs on your computer.

Online services work on the principle of "here and now", "take and use", as they say, "without leaving the checkout", more precisely, without leaving the Internet. While on the Internet, it is enough to open such an online service and use its services.

To find such services, it is enough to enter the search engine (Yandex, Google, etc.) the request for "character readers" and click on one of the first links that will be offered by the search engine.

In fig. 4 shows an example of a Sign-reader.

Rice. 4 We count the number of characters in the text online using the Sign Reader.

2) Copy this text to the clipboard, for example, using Ctrl + C.

3) We open the Sign-reader.

4) Paste text from the clipboard, for example, using the hot keys Ctrl + V.

6) A window will appear with the results of counting the number of characters (number 2 in Fig. 4).

Accurate character counting is necessary, for example, for freelancers, whose copywriters are paid precisely for the "volume", that is, for a certain number of characters with or without spaces in the written text. And it can also be useful to those users who need to put a certain number of characters in any service, no more than a certain number, for example, no more than 140 or 255 characters.

In some cases, the user is asked not to count the sum of values ​​in a column, but to count their number. That is, in simple terms, you need to count how many cells in a given column are filled with specific numeric or text data. Excel has a number of tools that can solve this problem. Let's consider each of them separately.

Depending on the user's goals, Excel can count all the values ​​in a column, only numerical data and those that meet a certain specified condition. Let's look at how to solve the assigned tasks in different ways.

Method 1: indicator in the status bar

This method is the simplest and requires the least amount of steps. It allows you to count the number of cells containing numeric and text data. You can do this simply by looking at the indicator in the status bar.

To complete this task, just hold down the left mouse button and select the entire column in which you want to count the values. As soon as the selection is made, in the status bar, which is located at the bottom of the window, near the parameter "Quantity" the number of values ​​contained in the column will be displayed. Cells filled with any data (numeric, text, date, etc.) will participate in the calculation. Empty items will be ignored when counting.

In some cases, the number of values ​​indicator may not appear in the status bar. This means that it is most likely disabled. To enable it, right-click on the status bar. A menu appears. In it, you need to check the box next to the item "Quantity"... After that, the number of cells filled with data will be displayed in the status bar.

The disadvantages of this method include the fact that the result obtained is not recorded anywhere. That is, as soon as you deselect it, it disappears. Therefore, if it is necessary to fix it, you will have to record the resulting total manually. In addition, using this method, you can count only all the cells filled with values ​​and you cannot set the counting conditions.

Method 2: COUNT operator

Using the operator COUNT as in the previous case, it is possible to count all the values ​​located in the column. But unlike the option with an indicator in the status bar, this method provides an opportunity to fix the result in a separate element of the sheet.

The main task of the function COUNT, which belongs to the statistical category of operators, is just counting the number of nonblank cells. Therefore, we can easily adapt it to our needs, namely, to count the elements of a column filled with data. The syntax for this function is as follows:

COUNT (value1; value2; ...)

In total, the operator can have up to 255 general group arguments "Meaning"... The arguments are just references to cells or a range in which you want to count values.

As you can see, unlike the previous method, this option offers to output the result to a specific element of the sheet with its possible saving there. But unfortunately the function COUNT all the same does not allow specifying the selection conditions for values.

Method 3: the COUNT operator

Using the operator CHECK only the numerical values ​​in the selected column can be counted. It ignores text values ​​and does not include them in the grand total. This function also belongs to the category of statistical operators, like the previous one. Its task is to count cells in the selected range, and in our case, in a column that contains numeric values. The syntax for this function is almost identical to the previous statement:

COUNT (value1; value2; ...)

As you can see, the arguments for CHECK and COUNT are exactly the same and represent cell or range references. The difference in syntax is only in the name of the operator itself.

Method 4: COUNTIF operator

Unlike the previous methods, the use of the operator COUNTIF allows you to set conditions that meet the values ​​that will take part in the calculation. All other cells will be ignored.

Operator COUNTIF also belongs to the statistical group of Excel functions. Its only task is to count non-empty elements in a range, and in our case in a column, that meet a given condition. The syntax for this operator is noticeably different from the previous two functions:

COUNTIF (range, criterion)

Argument "Range" is represented as a link to a specific array of cells, and in our case, to a column.

Argument "Criterion" contains the specified condition. It can be either an exact numeric or text value, or a value specified by signs. "more" (> ), "less" (< ), "not equal" (<> ) etc.

Let's count how many cells with the name "Meat" are located in the first column of the table.

Let's change the task a little. Now let's count the number of cells in the same column that do not contain the word "Meat".

Now let's count all the values ​​that are greater than 150 in the third column of this table.

Thus, we can see that there are a number of ways in Excel to count the number of values ​​in a column. The choice of a particular option depends on the specific goals of the user. So, the indicator on the status bar only allows you to see the number of all values ​​in the column without fixing the result; function COUNT provides the ability to record their number in a separate cell; operator CHECK only counts items containing numeric data; and using the function COUNTIF you can set more complex conditions for counting elements.

Hello dear readers.

Before starting this topic, I would like to advise you on an excellent educational product on the topic of Excel, called « Unknown Excel» , everything is clearly and qualitatively stated there. Recommend.

Now let's get back to the topic.

You are probably a representative of one of the professions in which you cannot do without the Excel program. After all, it allows you to make calculations, make lists, tables and diagrams, fill out diaries and solve many other tasks related to numbers.

However, not everyone who works with this application knows its full functionality and knows how to apply it in practice. Are you one of them? Then you've come to the right place. In particular, today we will analyze how to count the number of cells with a value in excel. There are several ways to do this. They depend on what kind of content you need to count. Let's analyze the most popular ones.

The fastest way

The simplest, but at the same time superficial, way is to count items in the status bar. Their number is displayed in the lowermost pane of the open window.

If you want to set certain simple parameters for calculations, open the status bar settings. This can be done by right-clicking on it. In the window that appears, pay attention to the part where it says "Average", "Quantity", "Number of numbers", "Minimum", "Maximum" and "Sum".

Select the option you want and learn more about what your table contains.

Count cells in rows and columns

There are two ways to find out the number of sections. The first one makes it possible to count them line by line in the selected range. To do this, you must enter the formula = ROWS (array) in the appropriate field. In this case, all cells will be counted, not just those containing numbers or text.

The second option - = NUMBERCOLUMN (array) - works by analogy with the previous one, but counts the sum of the sections in the column.

Count numbers and values

I will tell you about three useful things to help you work with the program.

We set the Excel conditions

An array is a range of items among which accounting is kept. It can only be a rectangular continuous collection of adjacent cells. The criterion is considered to be just the condition according to which the selection is performed. If it contains text or numbers with comparison signs, we enclose it in quotes. When the condition just equates to a number, the quotes are not needed.

Understanding the criteria

Examples of criteria:

• "> 0" - cells with numbers from zero and higher are counted;
• "Product" - sections containing this word are counted;
• 15 - you get the sum of the elements with the given digit.

For greater clarity, I will give a detailed example.

Logic problems

Do you want to set logical parameters to Excel? Use wildcards * and ?. The first one will denote any number of arbitrary characters, and the second one - only one.

For example, you need to know how many case-insensitive T cells in a spreadsheet have. We set the combination = COUNTIF (A1: D6; "T *"). Another example: you want to know the number of cells containing only 3 characters (any) in the same range. Then we write = COUNTIF (A1: D6; "???").

Averages and multiple formulas

Even a formula can be specified as a condition. Want to know how many sections you have that are above average in a certain range? Then you should write the following combination in the formula bar = COUNTIF (A1: E4; ">" & AVERAGE (A1: E4)).

If you need to count the number of filled cells by two or more parameters, use the COUNTIFS function. For example, you are looking for sections with data greater than 10, but less than 70. You write = COUNTIFS (A1: E4; "> 10"; A1: E4; "<70»).

In addition, you have the ability to set AND / OR conditions. Only in the second case you will have to use several rules at once. Look: you need to find cells in which words begin with the letter B or P - write = COUNTIF (A1: E4; "B *") + COUNTIF (A1: E4; "P *").

Perhaps, at first glance, the above instructions do not seem entirely clear to you. But having applied them several times in practice, you will see that they greatly simplify and improve the work with Excel.