Перейти к основному содержанию

Как подсчитать уникальные значения на основе нескольких критериев в Excel?

В этой статье я приведу несколько примеров для подсчета уникальных значений на основе одного или нескольких критериев на листе. Следующие подробные шаги могут вам помочь.

Подсчет уникальных значений на основе одного критерия

Подсчет уникальных значений на основе двух заданных дат

Подсчет уникальных значений на основе двух критериев

Подсчет уникальных значений на основе трех критериев


стрелка синий правый пузырь Подсчет уникальных значений на основе одного критерия

Например, у меня есть следующий диапазон данных, теперь я хочу подсчитать уникальный продукт, который продал Том.

количество документов уникально с несколькими критериями 1

Введите эту формулу в пустую ячейку, в которой вы хотите получить результат, например G2:

= СУММ (ЕСЛИ ("Том" = $ C $ 2: $ C $ 20, 1 / (COUNTIFS ($ C $ 2: $ C $ 20, «Том», $ A $ 2: $ A $ 20, $ A $ 2: $ A $ 20)) ), 0)), а затем нажмите Shift + Ctrl + Ввод вместе, чтобы получить правильный результат, см. снимок экрана:

количество документов уникально с несколькими критериями 2

Внимание: В приведенной выше формуле "Том»- это критерий названия, на основе которого вы хотите рассчитывать, C2: C20 содержит ли ячейки критерии имени, A2: A20 - это ячейки, в которых вы хотите подсчитать уникальные значения.


стрелка синий правый пузырь Подсчет уникальных значений на основе двух заданных дат

Чтобы вычислить уникальные значения между двумя заданными датами, например, я хочу подсчитать уникальный продукт в диапазоне дат от 2016/9/1 до 2016/9/30, примените эту формулу:

= СУММ (ЕСЛИ ($ D $ 2: $ D $ 20 <= ДАТА (2016, 9, 30) * ($ D $ 2: $ D $ 20> = ДАТА (2016, 9, 1)), 1 / СЧЁТЕСЛИ ($ A $ 2 : $ A $ 20, $ A $ 2: $ A $ 20, $ D $ 2: $ D $ 20, "<=" & DATE (2016, 9, 30), $ D $ 2: $ D $ 20, "> =" & DATE (2016, 9, 1))), 0), а затем нажмите Shift + Ctrl + Ввод ключи вместе, чтобы получить уникальный результат, см. снимок экрана:

количество документов уникально с несколькими критериями 3

Внимание: В приведенной выше формуле дата 2016,9,1 и 2016,9,30 - это дата начала и дата окончания, на основе которых вы хотите рассчитывать, D2: D20 содержит ли ячейки критерии даты, A2: A20 - это ячейки, из которых вы хотите подсчитать уникальные значения.


стрелка синий правый пузырь Подсчет уникальных значений на основе двух критериев

Если вы хотите подсчитать уникальный продукт, который Том продает в сентябре, вам может помочь следующая формула.

Введите эту формулу в пустую ячейку, чтобы вывести результат, например H2.

= SUM (IF (("Tom" = $ C $ 2: $ C $ 20) * ($ D $ 2: $ D $ 20 <= DATE (2016, 9, 30) * ($ D $ 2: $ D $ 20> = DATE ( 2016, 9, 1))), 1 / COUNTIFS ($ C $ 2: $ C $ 20, "Том", $ A $ 2: $ A $ 20, $ A $ 2: $ A $ 20, $ D $ 2: $ D $ 20, " <= "& DATE (2016, 9, 30), $ D $ 2: $ D $ 20,"> = "& DATE (2016, 9, 1))), 0) и затем нажмите Shift + Ctrl + Ввод ключи вместе, чтобы получить уникальный результат, см. снимок экрана:

количество документов уникально с несколькими критериями 4

Ноты:

1. В приведенной выше формуле «Том»- критерий названия, 2016,9,1 и 2016,9,30 две даты, по которым вы хотите рассчитывать, C2: C20 содержит ли ячейки критерии имени, и D2: D20 содержит ли ячейки дату, A2: A20 - это диапазон ячеек, в которых вы хотите подсчитать уникальные значения.

2. Если вам нужно использовать «or”Критерии для подсчета уникальных значений, например, для расчета продуктов, продаваемых Томом или в Южном регионе, примените следующую формулу:

=SUM(--(FREQUENCY(IF(("Tom"=$C$2:$C$20)+("South"=$B$2:$B$20), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), COUNTIF($A$2:$A$20, "<"&$A$2:$A$20))>0)), и не забудьте нажать Shift + Ctrl + Ввод ключи вместе, чтобы получить уникальный результат, см. снимок экрана:

количество документов уникально с несколькими критериями 5


стрелка синий правый пузырь Подсчет уникальных значений на основе трех критериев

Чтобы подсчитать уникальный продукт по трем критериям, формула может быть более сложной. Скажем, подсчет уникальных продуктов, которые Том продает в сентябре и в регионе Север. Пожалуйста, сделайте так:

Введите эту формулу в пустую ячейку, чтобы вывести результат, I2, например:

= SUM (IF (("Tom" = $ C $ 2: $ C $ 20) * ($ D $ 2: $ D $ 20 <= DATE (2016, 9, 30)) * ($ D $ 2: $ D $ 20> = DATE (2016, 9, 1)) * ("Север" = $ B $ 2: $ B $ 20), 1 / COUNTIFS ($ C $ 2: $ C $ 20, "Том", $ A $ 2: $ A $ 20, $ A $ 2 : $ A $ 20, $ D $ 2: $ D $ 20, "<=" & DATE (2016, 9, 30), $ D $ 2: $ D $ 20, "> =" & DATE (2016, 9, 1), $ B $ 2 : $ B $ 20, "Север")), 0), а затем нажмите Shift + Ctrl + Ввод ключи вместе, чтобы получить уникальный результат, см. снимок экрана:

количество документов уникально с несколькими критериями 6

Лучшие инструменты для офисной работы

Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

вкладка kte 201905


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
For all the above formula an you suggest a non array formula as my data runs to 25000 rows. I need a free suggestions and not paid ones
This comment was minimized by the moderator on the site
Hi,
This is great - except I cant get it to work for what I require
I have two sheets - a Summary sheet, and another sheet containing data
The dates are dynamic - so you enter the date ranges in the Summary sheet in two cells (from B2 to D2)
When I replace DATE(2022,6,1) with B2 it comes back with "a value used in the formula is the wrong data type"
When I test with putting DATE(2022,6,1) and DATE (2022,6,30) in the from - to parts in the formula - I get 0 as the result - which is wrong.
Note: I'm in Ireland - so the date format here is dd.mm.yy - changing things doesn't fix - and adds confusion tbh
My formula is
=SUM(IF(Sheet4!$C$2:Sheet4!$C$65<=(D2)*(Sheet4!$C$2:Sheet4!$C$65>=(B2)), 1/COUNTIFS(Sheet4!$A$2:Sheet4!$A$65, Sheet4!$A$2:Sheet4!$A$65, Sheet4!$C$2:Sheet4!$C$65, "<="&D2,Sheet4!$C$2:Sheet4!$C$65, ">="&B2))),0)
Where Sheet4 contains the data, C2:C65 are cells with dates, A2:A65 are cells with project numbers - where there maybe duplicates
Any help - greatly appreciated,
Thanks
This comment was minimized by the moderator on the site
1 month2 brand name 3 executive wise4 mix party nameCount unique party name
This comment was minimized by the moderator on the site
Count Unique Values Based On four Criteria
This comment was minimized by the moderator on the site
I am trying to use this method to calculate unique customers for a particular product (where a customer may have bought multiple times, but I want unique customers). If I enter the formula but limit the range to a subset of just 5 rows that I know contain a duplicate customer, it works fine. But when I apply to the whole column, e.g. $D:$D, it calculates endlessly; if it finishes, it returns a wrong result. But now it's not even finishing and I have to end the Excel process. Is this just too costly in terms of CPU to apply to a large volume of data (e.g. 1500 rows)?
This comment was minimized by the moderator on the site
I ma getting value in point which is not possible So please help me Out

{=SUM(IF(("Regular"='Raw Data'!$G$5:$G$1785)*('Raw Data'!$D$5:$D$1785<=DATE(2019,6,30)*('Raw Data'!$D$5:$D$1785>=DATE(2019,6,1))),1/COUNTIFS('Raw Data'!$B$5:$B$1785,'Raw Data'!$B$5:$B$1785,'Raw Data'!$D$5:$D$1785,"<="&DATE(2019,6,30),'Raw Data'!$D$5:$D$1785,">="&DATE(2019,6,1))),0)}
This comment was minimized by the moderator on the site
my question.
I mean that filtered rows , and not count hidden rows.
This comment was minimized by the moderator on the site
"if count visible rows."
I mean filtered rows , and not count rows hidden.
This comment was minimized by the moderator on the site
Ffrom this article formula,
if count visible rows. how can add or edit formula?
This comment was minimized by the moderator on the site
The greater and less than date criteria is a distracting example of how to use the sumif array.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations