Counting the number of unique values among duplicates. Count number of occurrences of value Excel count cells with specific value

Good afternoon everyone, today I open the "Functions" section and start with the COUNTIF function. To be honest, I didn’t really want to, because you can read about functions simply in the Excel help. But then I remembered my beginnings in Excel and realized what I needed. Why? There are several reasons for this:

There are many functions and the user often simply does not know what he is looking for, because. does not know the name of the function.
Functions are the first step to making life easier in Excel.

I myself used to, until I knew the COUNTIF function, added a new column, set the IF function, and then summed up this column.

Therefore, today I would like to talk about how, without unnecessary gestures, to find the number of cells that fit a certain criterion. So, the format of the function itself is simple:

COUNTIF("Range", "Criteria")

If with the first argument it is more or less clear, you can substitute a range of type A1:A5 or just the name of the range, then with the second it’s not so good, because the possibilities for setting the criterion are quite extensive and often unfamiliar to those who do not encounter logical expressions.

The simplest "Criteria" formats are:

The cell is strictly with a certain value, you can put the values ("apple"), (B4), (36). The case is not respected, but even an extra space will already include the cell in the count.
More or less than a certain number. Here the equal sign is already in use, more precisely inequalities, namely ("\u003e 5"); ("<>10");("<=103").

But sometimes we need more specific conditions:

Is there a text. Although someone may say that the function already counts only non-empty cells, but if you set the condition ("*"), then only text will be searched, numbers and spaces will not be taken into account.
Greater than (less than) the middle value of the range: (">"&AVERAGE(A1:A100))
Contains a certain number of characters, for example 5 characters:("???")
certain text that contained in cell: ("*sun*")
Text that starts with a specific word: ("But*")
Errors: ("#DIV/0!")
Boolean values ("TRUE")

If you have several ranges, each with its own criteria, then you need to use the COUNTIFS function. If the range is one, but there are several conditions, the easiest way is to sum: There is a more complex, although more elegant option - to use an array formula:

"The eyes are afraid, but the hands are doing"

COUNTIF function: count the number of cells by a certain criterion in Excel: 58 comments

blot
Tell me, how to find duplicate values, but what would there be no case sensitivity?
admin Post Author
in theory, COUNTIF just looks for matches, regardless of case.
blot
The fact of the matter is that it doesn’t find it - it doesn’t distinguish between uppercase and lowercase letters when searching, but combines them into a total number of matches ...
Tell me, maybe you need to put some character when searching? (apostrophes and quotation marks don't help)
Igor
How to set a condition in excel so that it considers a certain range of cells in rows with a certain value in the first column?
admin Post Author
Igor, actually COUNTING does this. Perhaps you'd better specify the task.
Anna
I liked the article, but alas, something doesn’t work out, I need to sum up repeated numbers from different numbers, for example 123 234 345 456 I need to calculate how many “1”, “2”, “3”, etc. in these numbers, that is, so that the formula recognizes the same numbers and counts them, if possible, write how to be? I will be waiting, Sincerely, Anna Irikovna
admin Post Author
Um. would be nice to see an example
But without it, I can give a hint - create a column next to it, where you will select numbers by groups through a text formula. For example, rightsimv(A1;1). And then work COUNTIF on this column.
Alex
How to spread the formula over a column so that the range in the formula remains the same, but the criterion changes?
admin Post Author
COUNTIF($A$1:$A$100,B1)
Anna
An example is, 23 12 1972, that is, this is the date of birth, I need the number of twos to be summed up, that is, not 2 + 2 + two, but that there are only three of them, that is, the final cell should contain 3, units 2, 3 7 9 one by one, is that possible? I just broke my head, I'm self-taught, but such formulas are too complicated for me, if it's not very difficult for you, please give a sample of the formula in full, at least for one number, with respect, Anna Irikovna
admin Post Author
Imagine that your date is in cell A1.

Then we count how many twos: \u003d dlstr (A1) - dlstr (SUBSTITUTE (A1; "2"; ""))

If it’s for several numbers, then it’s better to throw off an example, as you want it at the end, otherwise there are a lot of options.
Anna
thanks, I’ll try the formula now ... I’ll try it myself, if it doesn’t work out at all, then with a big bow I will ask for more advice)) Sincerely, Anna Irikovna
Anna
doesn’t work… can I send you what I need to your email address? it’s just a table .. it’s hard for me to describe it ... Anna I.
Anna
Max
Good article) But still I could not figure out my task. I have 3 columns, one is Students, second is Teachers, third is Grades. Tell me, how to calculate the number of students studying with Ivanova who received positive marks? It turns out like 2 ranges and 2 criteria, and I can't understand)
admin Post Author
Use COUNTIFS
Maykot
Good afternoon.
Help with range.
I have cell A2 which has a text value - for example "sun".
In cell A3 the value is "sea". Etc.
How to correctly enter the contents of cell A2 into the Formula =SUMIF(C:C;"*sun*",D:D) instead of a specific range ("*sun*"). not =SUMIF(C:C;"*sun*";D:D), but instead of "*sun*" was a cell reference?
admin Post Author
SUMMIF(C:C;"*"&$A$1&"*";D:D)
Dmitry
Alexander
Hello! Please tell me how to represent the formula in Excel:
Adjusted Cost =
\u003d Cost * (K1 + K2 + ... + KN - (N - 1);
where:
K1, K2, KN - coefficients other than 1
N is the number of coefficients other than 1.
Alexander
Hello! Please tell me the formula:
=SUMIF(C3:C14;"<1")-(СЧЁТЕСЛИ(C3:C14;"<1")-1)
set the range of coefficient values other than 1 (less than 1, more than 1, but less than 2).
Michael
Good evening!
Can you please tell me how to count the number of cells in which any date is indicated? That is, in the column there are cells with dates (different) and there are cells with text (different), I need to count the number of cells with dates.
Thank you!
admin Post Author
COUNTIF(F26:F29;"01/01/2016")
Will it go?
Julia
Good evening! Please tell me the formula that counts the numbers only which are more than 8 (processing in the time sheet). Here's the wrong one: =SUMIF(C42:V42;">8")+SUMIF(C42:V42)
admin Post Author
SUMIF(C42:V42,">8") =SUMIF(C1:C2;">8")

Don't forget that the English version uses commas between arguments.
D.N.
Good afternoon! thanks for the array formula for a range with multiple criteria!
I have a probably stupid question, but how to replace text (1;2;3) with cell references with text values.
that is, if I enter ("X"; "Y"; "Z") everything is correct
but when you enter (A1;A2;A3) - an error,
curly braces?)
Vitaly
Thank you. The article helped a lot.
Denis
Good afternoon. Faced such a problem in the COUNTIFS function. When you enter 2 ranges, everything counts perfectly, but when you add the 3rd, it gives an error. Could there be a catch in the number of cells?
I have =COUNTIFS('full-time'!R11C13:R250C13;"yes"; 'full-time'!R11C7:R250C7;"budget"; 'full-time'!R16C9:R30C9;"yes")
Without the 3rd range and conditions, everything is fine.
Thanks in advance.
admin Post Author
Denis, align the ranges, they should all be the same and you can set up to 127 sets of conditions.
A.K.
Hello, please help me figure it out.
There are two columns: one is the date, the second is the time (00:00:00 format).
It is necessary to select dates corresponding to a certain period of time.
At the same time, there should be 4 such intervals, i.e. every 6 hours.
Is it possible to set this with one formula, and if so, which one?
admin Post Author
Good afternoon.
Yes, you certainly may. True, I did not understand, do you need dates or hours? Two different formulas. And how do you want to break it down? To mark intervals with numbers? Like the first 6 hours of the day is 1, the second -2, etc.?
Andrew
Thanks in advance, but the question is:
given:
many rows, one column
green color indicates ready-made packages of documents, white - unfinished
the contents of the cells: different names of employees.
task: how to make a table using the formula: how many ready-made packages each employee has.
admin Post Author
Unfortunately, the color formula is not determined. It is more precisely defined, but there you need to write a custom formula
I would do this - filter by the color of green and put “ready” in the next column, then put “unfinished” - white cells in the same way.
Then, through COUNTING, I found everything I needed.
Cross
Hello! The question is. There are 4 columns, which respectively indicate students (column A), school number (column B), scores in chemistry (column C), scores in physics (column D). It is necessary to find the number of students of a certain school (for example, 5) who scored more points in physics than in chemistry. There are 1000 students in total. Is it possible to use any one formula to answer the question? I'm trying to use COUNTIFS, but it doesn't work.
admin Post Author
No, before using Countif, you will have to add one more column, where through IF to determine those who have more points in physics than in physics and then use COUNTIFS.
Cross
Okay thanks
Alexander
Good afternoon! The task in general is this. The table indicates line by line full name, sport, category. How to make a table on another tab that automatically counts how many dischargers are in each sport and which specific categories?
Alyona
Hello! Need help.
There is a column with dates of birth in the format 19740815, but you need to convert it to the format 08/15/1974
Thanks in advance.
admin Post Author
Good afternoon.

Well, the simplest - Text by columns - fixed width (4-2-2) - then add a column with the DATE function.
admin Post Author
Try COUNTIFS.
Hope
Hello.
Please tell me how to find the sum in the cells according to the condition:
There is a table, in the first column of which the product code is indicated, in the second the amount for it. There can be several lines with the same code. It is necessary to find the total (final) amount for each product.
Thanks in advance.
admin Post Author
Try SUMIF.
Dim Tell me what needs to be fixed?
COUNTIFS('Raw Data'!B2:B150;"Bucharest";'Raw Data'!E1:E150;"11/06/2014")
Faith
Good afternoon. Tell me, what criterion in the SUMIF formula should be set if you need to count the number of cells containing numbers from a range where there are both numbers and letters.
Thank you.
admin Post Author
Hello. That's just not going to work. Either an array formula or make another range where numbers will be searched through text functions. And then, through COUNTIF, you will find your result.
Novel
Good evening.
There is a column in which data is constantly added, you need to insert a number in the next column, how many times similar data has been encountered before this line
Anastasia
Good day to all!
There is an actual exit schedule for employees, all working hours are written in the format "09 * 21" - daytime full shift and "21 * 09" - night full shift.
There are also days with part-time shifts, which are considered to be paid at an hourly rate, for example "18 * 23" and so on.
All cells are in text format.

It is necessary that the formula calculates for each line (for each employee, respectively) the number of full shifts per month, ideally if it takes into account the criteria “09*21″+”21*09″, but you can use one criterion, then I just have these columns I will hide and combine them with a sum.

Through = count if I tried it, in the formula window the value is considered correct, and in the cell itself it displays a stupidly written formula, which format I just didn’t set - it doesn’t help.
I tried to replace 09 * 21 with 09:00 - 21:00 in the cells and the formula, respectively, but also in no way.
I put down both “09 * 21 *” and “* 09 * 21 *” in the formula - to no avail.

If you can do such a thing, provided that it will be written "09:00 - 21:00" - generally excellent, it will be easier for me to shovel one month, but then everything will be smooth)
and immediately the question is - is there a formula by which it will be possible to calculate the total number of hours in the range with all any values ("18:00 - 23:00", "12:45 - 13:45", etc.), except for the above "09:00 - 21:00" and "21:00 - 09:00" or count all cells where the number of hours is 12 and separately all where the number of hours is less than 12.

Thank you very much in advance, I've been scratching my head for a week! (((
admin Post Author
Try COUNTIF($A$1:A10;A10) - inserted into cell B10.
Natalia
The question has already been asked, but you answered via mail, could you repeat the answer already here?
“An example is this, 23 12 1972, that is, this is the date of birth, I need the number of twos to be summed up, that is, not 2 + 2 + two, but that there are only three of them, that is, in the final cell there should be 3, units 2, 3 7 9 by one, is this possible? I just broke my head, I'm self-taught, but such formulas are too complicated for me, if it's not very difficult for you, please give a sample of the formula in full, at least for one number, with respect, Anna Irikovna "
Faith
Good afternoon! I have a range of 30 cells in Excel, but I need to calculate the sum of only the first 20 cells. Help me please!
admin Post Author
Hello. And just to specify the range of the first 20 cells is not an option? Without a file, it's not clear to me what the difficulty is.

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

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

Number of values in the status bar

You can select the indicators displayed in a row by clicking on it with the right mouse button.

Status bar customization

If you need to use the number of values in further calculations, use the functions described below. For convenience of notation, we will assume that the data array for which the count is kept is . In your formulas, you can use the desired data range 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:

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

Counting the number of lines

COLUMN(array)- similar to the previous one, but counts the number of array columns

There is no function in Excel 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: =NUMROWS(array)*NUMBERCOLUMNS(array).

How to count blank cells in Excel

Counting empty cells

The function considers a cell 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

Counting numeric values

If you need to determine the number of cells containing values, 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 the cell contains a formula that returns zero or an empty string, the function will also include it in its result.

Count non-empty cells

How to count cells with a condition in Microsoft Excel

An array is a range of cells to be counted. You can only specify a rectangular range of adjacent cells;
Criteria - the condition by which the selection takes place. Write text conditions and numeric ones with comparison signs in quotation marks. We write equality to the number without quotes. For example:
- ">0" - count cells with numbers greater than zero
- "Excel" - count the cells in which the word "Excel" is written
- 12 - cell count with the number 12

Cell count with condition

If you need to consider several conditions, use the function COUNTIFS(array1, criterion1, [array2], [criteria1]…). A function can contain up to 127 array-criteria 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 occurs most often 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 than others in the list will be displayed. The function only works with numeric data.

Frequently occurring number

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

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

Counting the number of cells in Excel may be required in various cases. In this article, we will consider how to count blocks with certain values, empty and if they fall under the given conditions. We will use the following functions for this: COUNT, COUNTIF, COUNTIFS, COUNTNUT.

filled

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 various 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, 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 the consideration of functions.

Where numbers are entered

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

There are 20 blocks in total. The one in which the text is written was not taken into account, but those that are filled with the 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 goods were sold weighing more than 5 kilograms. To do this, you need to count how many blocks 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 with content greater than five.

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 references in Excel.

– number of cells with negative values: =COUNTIF(B2:B13;"<0″) ;
- the number of blocks in which the content 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. Let's select an area and specify "fruit" as a criterion. All blocks with 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, the 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 only apply to text.

Let's count how many products start 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 in the criteria "f?ukt" - 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 with 5 letters in the name, we will set “?????” as a criterion. .

If you set 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, their 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 in Excel the number of cells 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 ratings, average: 5,00 out of 5)

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

How to calculate the sum of values in the cells of an Excel table, for sure, every user who works in this program knows. This will help the SUM function, which is placed in a prominent place in the latest versions of the program, since, perhaps, it is used much more often than others. But sometimes the user may face a slightly different task - to find out the number of values with the given parameters in a certain column. Not their sum, but a simple answer to the question - how many times does the Nth value occur in the selected range? In Excel, you can solve this problem in several ways at once.

Which of the following methods is best for you depends largely on your goal and the data you're working with. Some operators are suitable only for numeric data, others do not work with conditions, and still others will not record the result in a table. We will tell you about all the methods, among which you will definitely find the one that best suits you.

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

Perhaps this is the easiest method that is suitable for working with text and numeric data. But he is not able to work with conditions.

Using this method is extremely simple: we select the data array of interest (in any convenient way). The result will immediately appear in the status bar (Quantity). All cells are included in the calculation, except for empty ones.

We emphasize once again that this method takes into account cells with any values. In theory, you can manually select only areas of interest in the table or even specific cells and see the result. But this is convenient only when working with small data arrays. For large tables, there are other ways, which we will discuss later.

Another disadvantage of this method is that the result is saved only until we deselect the cells. Those. you will either have to memorize or write the result somewhere separately.

Sometimes it happens that by default the “Quantity” indicator is not included in the status bar, but this can be easily fixed:

Method 2: Using the COUNTA Function

This function allows you to do the same, but with one significant difference - the result will be fixed. The remaining disadvantages of the previous method are preserved, i.e. COUNT3 does not work with conditions.

Function COUNT3 performs the task of counting all filled cells in the specified range (empty cells are not counted). The function formula may look different:

=COUNTA(cell1,cell2,...cellN)
=COUNTA(cell1:cellN)

In the first case, the function will count all the listed cells. In the second, it will determine the number of non-empty cells in the range from cell 1 to cell N. Please note that the number of function arguments is limited at around 255.

Let's try to apply the COUNT3 function with an example:

Method 3: Using the COUNT Function

The COUNT function is suitable if you work exclusively with numbers. Cells filled with text values will not be taken into account by this function. Otherwise, COUNT is almost identical to COUNTA from the previously discussed method.

This is how the COUNT function formula looks like:

=COUNT(cell1,cell2,...cellN)
=COUNT(cell1:cellN)

The algorithm of actions is also similar to the one we discussed above:

Method 4: COUNTIF Operator

Finally, we come to a function that is able to make a calculation according to certain conditions. Operator COUNTIF created just for this task. All cells without data and those that do not meet user-specified parameters are ignored by this function.

The COUNTIF syntax is typical for all conditional operators:

=COUNTIF(range, criteria)

Range - the area of cells within which you need to find the number of matches according to a given condition.

A criterion is a specific condition that the function is looking for a match on. The condition is specified in quotation marks, can be specified as an exact match with the entered number or text, or as a mathematical comparison, specified by the signs "not equal" ("<>”), “greater than” (“>”) and “less than” (“<»). Также предусмотрена возможность добавить условия «больше или равно» / «меньше или равно» («=>/=<»).

Let's analyze the application of the function COUNTIF:

For a better understanding of how to work with the COUNTIF function, let's try to change the condition:

Finally, you can analyze the work with numerical conditions containing the signs "greater than" (">") or "less than" ("<»). Давайте, например, выясним сколько раз в столбце “Продано” встречается значение больше 350.

Method 5: Using the COUNTIFS Operator

COUNTIFS - a function completely similar to COUNTIF, but allows you to enter several ranges with the same or different conditions.

Let's see how to do this using the WHATIFS function:

Method 6: COUNTBLANK function

In some cases, we may be faced with the task of counting only empty cells in the data array. Then the function will be extremely useful COUNTBLANK, which will ignore all cells except empty ones.

The syntax of the function is very simple:

The procedure is practically no different from the above:

Conclusion

Microsoft Excel is an extremely powerful tool that can solve many problems of various levels of complexity. Even in the simplest task of counting the number of values, the program can make life much easier for the user, and in several different ways. Which method of counting the number of values listed in this article to stop on depends on your goals and objectives.

Very often, when working in Excel, you need to count the number of cells on a worksheet. These can be empty or filled cells containing only numeric values, and in some cases, their contents must meet certain criteria. In this tutorial, we will take a detailed look at two main Excel functions for counting data − CHECK and COUNTIF, as well as get acquainted with the less popular - ACCOUNTS, 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 range cells 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 non-empty cells in a range, you can use the statistical function ACCOUNTS. Cells containing text, numeric values, date, time, and boolean values TRUE or FALSE are considered non-empty.

COUNTIF()

statistical function COUNTIF allows you to count the cells of an Excel worksheet using various types of conditions. For example, the formula below returns the number of cells that contain negative values:

The following formula returns the number of cells whose value is 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 containing the word "text" and is case insensitive.

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

Function COUNTIF allows you to use even 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 whose values are greater than zero but less than 50:

Function COUNTIFS allows cells to be counted using a 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 a letter BUT or with a letter To:

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

Counting the number of unique values ​​among duplicates. Count number of occurrences of value Excel count cells with specific value

Post navigation