Countif excel как пользоваться - Учим Эксель

КАК: Как подсчитать данные в избранных ячейках при помощи Excel COUNTIF — 2021

Как посчитать количество текстовых повторов в таблице Excel, функция СЧЕТЕСЛИ (Октябрь 2021).

Table of Contents:

COUNTIF функция соединяет воединыжды функцию IF и функцию COUNT в Excel; эта композиция дозволяет подсчитать количество раз, когда определенные данные находятся в избранной группе ячеек. ЕСЛИ часть функции описывает, какие данные соответствуют обозначенным аспектам, и COUNT часть делает подсчет.

Синтаксис функции COUNTIF для Excel

В Excel синтаксис функции относится к компоновке функции и включает имя, скобки и аргументы функции. Синтаксис для COUNTIF функция последующая:

= COUNTIF (спектр, аспекты)

Аргументы функции указывают функции, для какого условия мы тестируем и какой спектр данных рассчитываем при выполнении условия.

Диапазон — Группу ячеек делают функцию поиска.

аспекты — Значение по сопоставлению с данными в Ячейки спектра, Если совпадение найдено, подсчитывается ячейка в спектре. Для этого аргумента можно ввести фактические данные либо ссылку на ячейки для данных.

Ввод данных примера

Следуя инструкциям, приведенным в данной для нас статье, вы сможете сделать и применять функцию COUNTIF, показанную на изображении выше, чтоб подсчитать количество торговых представителей с наиболее чем 250 заказами.

Первым шагом к использованию функции COUNTIF в Excel является ввод данных. Введите данные в ячейки С1 в E11 листа Excel, как показано на изображении выше.

Функция COUNTIF и аспекты поиска (наиболее 250 заказов) будут добавлены в строчку 12 ниже данных.

В инструкциях по обучению не указаны шаги форматирования для рабочего листа; это не помешает окончить учебное пособие. Ваш рабочий лист будет смотреться по другому, чем показанный пример, но функция COUNTIF даст для вас те же результаты.

Создание функции COUNTIF

Хотя можно ввести COUNTIF функции в ячейку на листе, почти всем людям проще применять Formula Builder для входа в функцию.

Если вы используете наиболее старенькую версию Excel, Formula Builder могут быть изменены Аргументы функции диалоговое окно; просто вставьте данные таковым же образом, как в данной для нас статье.

  1. Нажмите на ячейку E12 чтоб создать его активной ячейкой — тут мы войдем в функцию COUNTIF.
  2. Нажми на Формулы вкладку ленты.
  3. изберите Доп функции> Статистические от ленты.
  4. Нажмите на COUNTIF в перечне, чтоб Formula Builder.

Данные, которые мы вводим в две пустые строчки в диалоговом окне, образуют аргументы функции COUNTIF. Эти аргументы говорят функции, какое условие мы тестируем и какие ячейки подсчитываем при выполнении условия.

Аргумент спектра

Диапазон аргумент гласит COUNTIF функцию, которую группа ячеек отыскивает, пытаясь отыскать обозначенные аспекты.

  1. в Formula Builder, нажми на Диапазон линия.
  2. Выделите ячейки E3 в E9 на листе для ввода этих ссылок на ячейки в качестве спектра для поиска функцией.

Аспект Аргумент

аспекты аргумент докладывает COUNTIF, какие данные он должен попробовать отыскать в Диапазон аргумент. Хотя фактические данные — к примеру, текст либо числа, такие как > 250 могут быть введены в диалоговое окно для этого аргумента, обычно идеальнее всего ввести ссылку на ячейку в диалоговое окно, к примеру D12 а потом введите данные, которые мы желаем сравнить, в эту ячейку на листе.

  1. Нажми на аспекты линия.
  2. Нажмите на ячейку D12 для ввода данной для нас ссылки на ячейку. Функция будет находить спектр, избранный на прошлом шаге, для данных, которые соответствуют хоть каким данным, введенным в эту ячейку.
  3. Нажмите Готово для закрытия диалогового окна и окончания функции.

Ответ на нуль должен показаться в ячейке E12 — ячейка, в которую мы вошли, — поэтому что мы еще не добавили данные в поле Criteria (D12).

= СЧЕТЕСЛИ (Е3: Е9, Д12) 04 из 04

Добавление критериев поиска

Крайний шаг в учебнике — добавить аспекты, по которым мы желаем, чтоб функция соответствовала. В этом случае мы желаем, чтоб количество торговых представителей с наиболее чем 250 заказами на год подсчитывалось. Для этого мы вводим > 250 в D12 — ячейка, идентифицированная в функции, содержащая аргумент критериев.

  1. В ячейке D12 тип > 250 и нажмите Войти на клавиатуре.
  2. Число 4 обязано показаться в ячейке E12.

Как организовать и отыскать данные при помощи сводных таблиц Excel

Как организовать и найти данные с помощью сводных таблиц Excel

Функция сводной таблицы Excel Excel упрощает компанию и извлечение инфы из таблиц данных без использования сложных формул.

Как подсчитать все типы данных при помощи COUNTA в Excel

Как подсчитать все типы данных с помощью COUNTA в Excel

Узнайте, как применять функцию COUNTA в Excel для подсчета ячеек, содержащих любые типы данных, такие как текст, даты чисел и т. Д., Игнорируя пустые ячейки.

Как скопировать формулы и данные при помощи Fill Handle Excel

Как скопировать формулы и данные с помощью Fill Handle Excel

Ускорьте собственный рабочий процесс, научившись применять дескриптор наполнения Excel для копирования формул, форматирования и данных на вашем десктопе и в рабочей книжке.

Excel COUNTIFS Function

Excel COUNTIFS function

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>, ,=) and wildcards (*,?) for partial matching.

  • range1 — The first range to evaulate.
  • criteria1 — The criteria to use on range1.
  • range2 — [optional] The second range to evaluate.
  • criteria2 — [optional] The criteria to use on range2.

The COUNTIFS function in Excel counts the number of cells in a range that match one supplied criteria. Unlike the older COUNTIF function, COUNTIFS can apply more than one condition at the same time. Conditions are supplied with range/criteria pairs, and only the first pair is required. For each additional condition, you must supply another range/criteria pair. Up to 127 range/criteria pairs are allowed.

Интересно почитать:  В excel добавить вкладку

Criteria can include logical operators (>, ,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below.

COUNTIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIFS requires a cell range for range arguments, you can’t use an array.

Basic example

With the example shown, COUNTIFS can be used to count records using 2 criteria as follows:

Notice the COUNTIFS function is not case-sensitive.

Double quotes («») in criteria

In general, text values need to be enclosed in double quotes, and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes as shown below:

Note: showing one condition only for simplicity. Additional conditions must follow the same rules.

Value from another cell

When using a value from another cell in a condition, the cell reference must be concatenated to an operator when used. In the example below, COUNTIFS will count the values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes, but the cell reference is not:

Note: COUNTIFS is one of several functions that split conditions into two parts: range + criteria. This causes some inconsistencies with respect to other formulas and functions.

Not equal to

To construct «not equal to» criteria, use the «<>» operator surrounded by double quotes («»). For example, the formula below will count cells not equal to «red» in the range A1:A10:

Blank cells

COUNTIFS can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

Dates

The easiest way to use COUNTIFS with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than a date in B1, you can use a formula like this:

Notice we concatenate the «>» operator to the date in B1, but and are no quotes around the cell reference.

The safest way to hardcode a date into COUNTIFS is with the DATE function. This guarantees Excel will understand the date. To count cells in A1:A10 that contain a date less than September 1, 2020, you can use:

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (

) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in A1:A5 that contain the text «apple» anywhere, you can use a formula like this:

) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (

), add a tilde in front of the wildcard (i.e.

OR logic

The COUNTIFS function is designed to apply multiple criteria, but conditions are applied with AND logic. This means if you try to count cells that contain «red» or «blue» in the same range, the result will be zero (0). However, to count cells with OR logic, you can use an array constant and the SUM function like this:

The formula above will count cells in range that contain «red» or «blue». Briefly, COUNTIFS returns two counts in an array (one for «red» and one for «blue») and the SUM function returns the sum as a final result. For more information, see this example.

How to Count Text in Excel

Microsoft Word allows you count text, but what about Excel? If you need a count of your text in Excel, you can get this by using the COUNTIF function and then a combination of other functions depending on your desired result.

How to count text in Excel

If you want to learn how to count text in Excel, you need to use function COUNTIF with the criteria defined using wildcard *, with the formula: =COUNTIF(range;»*») . Range is defined cell range where you want to count the text in Excel and wildcard * is criteria for all text occurrences in the defined range.

Some interesting and very useful examples will be covered in this tutorial with the main focus on the COUNTIF function and different usages of this function in text counting. Limitations of COUNTIF function have been covered in this tutorial with an additional explanation of other functions such as SUMPRODUCT/ISNUMBER/FIND functions combination. After this tutorial, you will be able to count text cells in excel, count specific text cells, case sensitive text cells and text cells with multiple criteria defined – which is a very good base for further creative Excel problem-solving.

Count Text Cells in Excel

Text Cells can be easily found in Excel using COUNTIF or COUNTIFS functions. The COUNTIF function searches text cells based on specific criteria and in the defined range. As in the example below, the defined range is table Name list, and text criteria is defined using wildcard “*”. The formula result is 5, all text cells have been counted. Note that number formatted as text in cell B10 is also counted, but Booleans (TRUE /FALSE) and error (#N/A) are not recognized as text.

Интересно почитать:  Как научиться работать в excel

The formula for counting text cells:

For counting non-text cells, the formula should be a little bit changed in criteria part:

If there are several criteria for counting cells, then COUNTIFS function should be used. For example, if we want to count the number of employees from Texas with project number greater than 20, then the function will look like:

In criteria range in column State, a specific text criteria is defined under quotations “Texas”. The second criteria is numeric, criteria range is column Number of projects, and criteria is numeric value greater than 20, also under quotations “>20”. If we were looking for exact value, the formula would look like:

Count Specific Text in Cells

For counting specific text under cells range, COUNTIF function is suitable with the formula:

The first part of the formula is range and second is text criteria, in our example “*Mike*”. If wildcard * has not been used before and after criteria text, formula result would have been 1 (Formula would find cells only with word Mike). Wildcard * before and after criteria text, means that all cells that contain criteria characters will be taken into account. As in another example below with text criteria Sun, three cells were found ( sun , Sun ny, sun is shining)

Note: The COUNTIF function is not case sensitive, an alternative function for case sensitive text searches is SUMPRODUCT/FIND function combination.

Count Case Sensitive Specific Text

For a case-sensitive text count, a combination of three formulas should be used: SUMPRODUCT, ISNUMBER and FIND . Let’s look in the example below. If we want to count cells that contain text Sun, case sensitive, COUNTIF function would not be the appropriate solution, instead of this function combination of three functions mentioned above has to be used.

We should go through a separate function explanation in order to understand functions combination. FIND function , searches specific text in the defined cell, and returns the number of the starting position of the text used as criteria. This explanation is relevant, if the searching range is just one cell. If we want to use FIND function in a range of the cells, then the combination with SUMPRODUCT function is necessary.

Without ISNUMBER, function combination of FIND and SUMPRODUCT functions would return an error. ISNUMBER function is necessary because whenever FIND function does not match defined criteria, the output will be an error, as in print screen below of the evaluated formula.

In order to change error values with Boolean TRUE/FALSE statement, ISNUMERIC formula should be used (defining numeric values as TRUE, and non-numeric as FALSE, as in print screen below).

You might be wondering what character in SUMPRODUCT function stands for. It converts Boolean values TRUE/FALSE in numeric values 1/0, enabling SUMPRODUCT function to deal with numeric operations (without character — in SUMPRODUCT function, the final result would be 0).

Remember, if you want to count specific text cells that are not case sensitive, COUNTIF function is suitable. For all case sensitive searches combination of SUMPRODUCT/ISNUMBER/FIND functions is appropriate.

Count Text Cells with Multiple Criteria

If you want to count cells with Multiple criteria , with all criteria acceptable, there is an interesting way of solving that problem, a combination of SUMPRODUCT/ISNUMBER/FIND functions. Please take a look in the example below. We should count all cells that contain either Mike or $. Tricky part could be the cells that contain both Mike and $.

Formula just looks complex, in order to be easier for understanding, I will divide it into several steps. Also, knowledge from the previous tutorial point will be necessary for further work, since the combination of FIND, ISNUMBER, and SUMPRODUCT functions have been explained.

In the first part of the function, we loop through the table and find cells that contain Mike:

The output of this part of the function will be an array with values <1;0;0;0;1;0;0;0;1>, number 1, where criteria have been met, and 0, where has not.

In the second part of the function, looping criteria is $, counting cells containing this value:

The output of this part of the function will be an array with values <1;0;0;1;1;0;0;0;1>, number 1, where criteria have been met, and 0, where has not.

Next step is to sum these two arrays, since cell should be counted if any of conditions is fulfilled:

The output of this step is <2;0;0;1;2;0;0;0;2>, the number greater than 0 means that one of the condition has been met (2 – both conditions, 1 – one condition)

Without function part >0, the final function would double count cells that met both conditions and the final result would be 7 (sum of all array numbers). In order to avoid it, in the formula should be added >0:

Интересно почитать:  Перенос в эксель

The output of this step is array <1;0;0;1;1;0;0;0;1>, the previous array has been checked and only values greater than 0 are TRUE (in an array have value 1), and others are FALSE (in an array have value 0).

Final output of the formula is the sum of the final array values, 4.

Looks very confusing, but after several usages, you will become familiar with this functions.

At the end, we will cover one more multiple criteria text count function, already mentioned in the tutorial, COUNTIFS function. In order to distinguish the usage of functions mentioned above and COUNTIFS function, two words are enough OR/AND. If you want to count text cells with multiple criteria but all conditions have to be met at the same time, then COUNTIFS function is appropriate. If at least one condition should be met, then the combination of function explained above is suitable.

Look at the example below, the number of cells that contain both Mike and $ is easily calculated with COUNTIFS function:

In the defined range, function counts only cells where both conditions have been met. The final result is 3.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.

How to Use Excel COUNTIF Function (Examples + Video)

Simple calculation such as adding the values in a range of cells or counting the values in reach of cells is something that you would have to do when working with data in Excel.

And in some cases, you may have to count only those cells that meet a specific criterion.

And you can easily do that with the COUNTIF function in Excel

In this tutorial, I will show you how the Excel COUNTIF function works with simple examples add a detailed explanation

This Tutorial Covers:

Let’s first look at the syntax of the COUNTIF function:

Excel COUNTIF Function Syntax

  • range is the range of cells where you want to count cells that meet the condition
  • criteria is the condition that must be evaluated against the range of cells for a cell to be counted.

Excel COUNTIF Function Examples

Now let’s have a look at some examples that will show you how to use the COUNTIF function in Excel.

Count Cells With a Specific Text String

With the COUNTIF function, you can count all the cells that contain a specific text string.

Suppose you have a dataset as shown below and you want to count all the cells that have the text Printer in it.

Dataset for counting text and greater less than

Here is the formula that will do this:

COUNTIF Formula to count the number of times printer repeats

The above formula uses the text I specified as the second argument as the criteria and counts all the cells that have the same text (which is “Printer”)

In this example, I have manually entered the criteria text, but you can also refer to a cell that contains the criteria text.

Note: Criteria text in the COUNTIF formula is not case sensitive. So I can also use ‘printer’ or ‘PRINTER’, as the result would still be the same

Count Cells Value Greater than or Less than

Just like I used the COUNTIF function with text, I can also use it with cells containing numbers.

Suppose I have a dataset as shown below and I want to count all the cells where the number in column B is greater than 30.

Dataset for counting text and greater less than

Below is the formula that will do this:

COUNTIF formula to count cells with value greater than 30

The above formula uses the greater than an operator with the number as the criteria. This tells Excel to only consider those cells where the value is more than 30.

You can also use other operators such as less than (<). equal to (=), and not equal to (<>) in the COUNTIF criteria.

Count Cells that Contain Text String

While there is the COUNTA function that counts the cells that contain numbers, there is no in-built formula that can count only those cells that contain a text string.

But it can easily be done using the COUNTIF function.

Suppose you have a dataset as shown below and you only want to count the number of cells that are text (and ignore the numbers).

Dataset to count text cells

Here is the formula that will do this:

formula to count cells that contain text

The above formula uses an asterisk (which is a wildcard character). An asterisk represents the text of any length.

So this criteria would count all the cells where there is any text string (of any length). In case the cells are empty/blank or have numbers in them, then those would not be counted.

Some Additional Notes

  • Criteria could be a number, expression, cell reference, text, or a formula.
    • Criteria which are text or mathematical/logical symbols (such as =,+,-,/,*) should be in double-quotes.
    • A question mark (?) matches any single character
    • An asterisk matches (*) any sequence of characters.
    • If you want to find an actual question mark or asterisk, type a tilde (

    Related Excel Functions:

    You May Also Like the Following Tutorials:

Ссылка на основную публикацию
Adblock
detector