В столбце A у меня есть список разных имен. В столбце B у меня есть значения 0 или 1.
Я хочу получить количество всех уникальных имен из столбца A, которые имеют 1 в столбце B.
Используя формулу ниже массива, я могу считать уникальные имена, но не могу применить условие к столбцу B.
=SUM(1/COUNTIF(A:A,A:A))
Предполагая, что не более 100 строк попробуйте эту "формулу массива" для подсчета имен разных в A2: A100, где в строке B2 есть: 1 в той же строке: B100:
=SUM(IF(FREQUENCY(IF(B2:B100=1,IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))),ROW(A2:A100)-ROW(A2)+1),1))
подтверждено CTRL+SHIFT+ENTER
Обратите внимание, что я говорю, что разные не уникальны, поскольку два не одинаковы.
Как это?
=SUM(--(FREQUENCY(IF(($B$1:$B$8=1), COUNTIF($A$1:$A$8, "<"&$A$1:$A$8), ""), COUNTIF($A$1:$A$8, "<"&$A$1:$A$8))>0)))
Это формула массива. Вам нужно нажать CTL + SHIFT + ENTER
Снимок экрана
Должна ли быть формула? Очень простой способ сделать это - создать сводную таблицу из столбцов A и B. Затем установите столбец B как поле фильтра и значение A в качестве значений (вам нужно пометить столбцы A и B). Тогда A4 на сводном листе (по крайней мере, по умолчанию) должен содержать ваш ответ. Это будет работать для любого списка размеров и действительно будет работать при наличии нескольких условий.
Если это должна быть формула, она должна быть полностью такой? Если вам разрешено сортировать по A до того, как ваша формула работает, то как насчет этого рабочего процесса. Я не сумасшедший об этом, и я подозреваю, что это может быть улучшено.
1) Сортировка по A (по возрастанию или по убыванию), затем по B (по убыванию)
2) В C каждая строка C содержит такую формулу:
=if (and(A2<>A1,B2=1),1,0)
C1 будет выглядеть следующим образом:
=b1
Перетащите с C2 до последней строки данных (скажем, c500).
3) Затем подсчитываем, суммируя C, = sum (c1: c500).
Вы можете, конечно, перетащить формулу под строками данных (пока вы знаете максимальное количество, которое вам нужно), оно должно возвращать 0 для всех пустых строк. Затем, когда вы добавите данные в и B, остальные будут обновляться автоматически. Вероятно, вам также придется прибегнуть к данным.
Одна приятная вещь в решении сводной таблицы заключается в том, что она может автоматически расти с вашим набором данных, пока вы вставляете новые данные в строки существующих данных. Конечно, сортировка не требуется.
Относительно простым решением этой общей проблемы является
= СУММ ((В: В = 1)/COUNTIFS (A: A, A: A, B: B, B: B))
введен как формула массива.
Вам нужно будет ограничить размер массивов, где у вас действительно есть данные.
Легко с сводной таблицей......:
