Подсчет нескольких критериев

Вам не нужно ничего вкладывать. Функция SUMPRODUCT очень мощный, который позволяет вам считать или суммировать на основе очень сложных условий. Он поддерживает как логические операторы AND, так и OR.

TL; DR версия

=SUMPRODUCT((($A$2:$A$10="Apple")+($A$2:$A$10="Pear"))*(($B$2:$B$10="Red")+($B$2:$B$10="Green"))*($C$2:$C$10<>"Yes"))

Длинная версия (извините...)

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

В основном, SUMPRODUCT содержит несколько членов, каждый из которых является условием. Если вы умножите эти условия, вы получите логическое И. Если вы добавите эти условия, вы получите логическое ИЛИ.

С примером гораздо проще! Итак, вот разбивка окончательной формулы на примере ваших фруктов.

Примечание: для упрощения я использовал таблицу имен Fruits, что позволяет использовать именованные диапазоны и улучшает читаемость.

Так вот как это работает. Ваше первое условие на самом деле является логическим ИЛИ (вам нужны либо яблоки, либо груши, поскольку фрукт не может быть и тем и другим. Таким образом, формула (1) имеет 2 члена или 2 условия: Fruit = Apple, Fruit = Pear.

(Fruits[Fruit]="Apple") вернусь TRUE если это яблоко, FALSE иначе. Суммируя это для 2-го члена ((Fruits[Fruit]="Pear")), вы получите массив 0 и 1. Логика следующая:

• Ложь + Ложь = 0
• True + False = 1
• True + True = 1

Затем SUMPRODUCT суммирует все элементы этого массива (0 и 1). Что в основном так же, как подсчет элементов. Результат равен 8, как и ожидалось (ячейка D14).

2-е условие (цвет) работает точно так же. Если вы хотите включить третий цвет (например, "оранжевый"), вы просто добавите новый элемент в уравнение с +,

Третье условие более простое, у вас есть только 1 критерий. Я использовал <> подписать специально, чтобы показать вам, что вы также можете исключить элементы. В формуле (1) вы могли бы использовать, например, (Fruits[Fruit]<>"Banana"),

Важно: обратите внимание, что в SUMPRODUCT когда есть только один член (как в 3-м уравнении), вам нужно преобразовать результат в число. Помните: SUMPRODUCT создает массив TRUE а также FALSE, Вы можете сделать это легко с помощью N(...) функция или альтернативно написание --(...), который преобразует TRUE в 1, и FALSE в 0.

Теперь эти 3 условия работают индивидуально, но мы хотим объединить их в 1 формулу. И поскольку мы хотим, чтобы каждое из этих трех условий было истинным (фруктовое, цветное и ядовитое), нам необходимо создать логическое И. Это можно сделать так же, как мы делали ИЛИ, но на этот раз мы будем использовать умножение (*):

• Ложь * Ложь = 0
• True * False = 0
• True * True = 1

Для наших 3 условий мы просто используем 3 отдельные формулы, которые мы написали ранее, и помещаем их в SUMPRODUCT, Эти 3 члена должны быть заключены в круглые скобки и разделены * (мы в основном умножаем их).

Вот последние формулы, которые вы можете попробовать:

=SUMPRODUCT((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))
=SUMPRODUCT((Fruits[Color]="Red")+(Fruits[Color]="Green"))
=SUMPRODUCT(N(Fruits[Poisonous]<>"Yes"))

И последний:

=SUMPRODUCT(((Fruits[Fruit]="Apple")+(Fruits[Fruit]="Pear"))*((Fruits[Color]="Red")+(Fruits[Color]="Green"))*(Fruits[Poisonous]<>"Yes"))

Примечания стороны:

• Каждое проверяемое условие (например, Color = Red) должно быть заключено в круглые скобки: (Fruits[Color]="Red")
• Порядок скобок очень важен, если у вас есть условия ИЛИ. Например, уравнение (X and (Y or Z)) это не то же самое, что ((X and Y) or Z),
• Вы можете использовать классические операторы для проверки условия: = для равных, <> для разных, > и <для большего / меньшего, чем>> и <= для большего / меньшего или равного.
• Мы использовали SUMPRODUCT считать, но мы также можем использовать его для суммирования вещей. Если один из членов уравнения не имеет = знак, то значения принимаются во внимание (см. пример ниже, где столбец G суммируется).
• Критерии "Apple" можно заменить ссылкой на ячейку, которая сама по себе может быть выпадающим меню. Лучше использовать переменные, а не писать текст непосредственно внутри формулы.
• SUMPRODUCT может быть ресурсоемкой формулой, поскольку она производит умножения и суммы... В зависимости от того, сколько условий вы тестируете, насколько велик набор данных и сколько раз вы используете SUMPRODUCT, Для более простых условий, SUM.IFS вероятно быстрее.
• SUMPRODUCT также поддерживает частичный текстовый поиск, как показано ниже:

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

У вас есть в основном 2 способа подсчета значений:

• Либо используя "белый список", чтобы перечислить приемлемые значения, используя ISNUMBER
• Либо с помощью "balcklist", чтобы исключить недопустимые значения, используя ISNA

Whitelist:

=SUMPRODUCT(
    (ISNUMBER(MATCH(Fruits[Fruit],Whitelist[Fruit];0)))
    *
    (ISNUMBER(MATCH(Fruits[Color],Whitelist[Color];0)))
    *
    (ISNUMBER(MATCH(Fruits[Poisonous],Whitelist[Poisonous];0)))
)

Черный список:

=SUMPRODUCT(
    (ISNA(MATCH(Fruits[Fruit],Blacklist[Fruit],0)))
    *
    (ISNA(MATCH(Fruits[Color],Blacklist[Color],0)))
    *
    (ISNA(MATCH(Fruits[Poisonous],Blacklist[Poisonous],0)))
)

Я настоятельно советую вам не использовать это в столбце, поскольку это может быть ресурсоемким. Я не пробовал это на очень большом списке значений, но не стесняйтесь поделиться своими результатами!

Я не уверен, что полностью понимаю это, но кажется возможным получить ответ с

=SUMPRODUCT(COUNTIF($E$2:$E$3,$A$2:$A$10), COUNTIF($F$2:$F$4,$B$2:$B$10), COUNTIF($G$3,$C$2:$C$10))

Чтобы попытаться объяснить: как мы знаем COUNTIF($B$2:$B$10, "Red")(или, что эквивалентно, COUNTIF($B$2:$B$10, $F$2) считает красный плод, получая счет 3. Точно так же, COUNTIF($B$2:$B$10, "Green")(или, что эквивалентно, COUNTIF($B$2:$B$10, $F$3) считает зеленый плод, получая счет 5. Так что интуитивно заманчиво попытаться использовать COUNTIF($B$2:$B$10, $F$2:$F$3)считать фрукты, которые красные или зеленые. Это не работает само по себе, но мы можем заставить его работать, делая

=SUM(COUNTIF($B$2:$B$10, $F$2:$F$3))

как формула массива, или

=SUMPRODUCT(COUNTIF($B$2:$B$10, $F$2:$F$3))

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

Но приведенные выше формулы в точности соответствуют тому, что вы ожидали в предыдущем обсуждении: они добавляют 3+5 и получают 8. Это важно: они добавляют массив из двух чисел, потому что мы смотрим на два цвета. Это создает нам проблемы, когда мы ищем разное количество значений в разных столбцах.

COUNTIF($E$2:$E$3, "Yellow"), COUNTIF($E$2:$E$3, "Red") а такжеCOUNTIF($E$2:$E$3, "Green") на первый взгляд, кажется, не имеет такого большого смысла; они имеют значения 0, 1 и 1 соответственно. Но это означает, что COUNTIF($E$2:$E$3,$A$2:$A$10) производит массив из девяти чисел, а именно:0, 1, 1, 1, 1, 1, 1, 1, 1 и 1. Угадайте, что? Сумма еще 8.

Помни как SUMPRODUCT работает. SUMPRODUCT(X11:X14, Y11:Y14, Z11:Z14)например, равно

(X11 × Y11 × Z11) +
(X12 × Y12 × Z12) +
(X13 × Y13 × Z13) +
(X14 × Y14 × Z14)

поэтому он должен иметь аргументы массива одинаковой длины. Два, два и один - проблема; 15, 26, и какой-то другой номер является проблемой. Девять, девять и девять это хорошо.

Теперь посмотрим на X, Y, Z Например, но сделайте эти замены:

• X11 → A2 это один из фруктов, которые мы ищем,
• Y11 → B2 это один из цветов, которые мы ищем,
• Z11 → C2 нет",

и так далее вниз. Поскольку TRUE равно 1, а FALSE равно 0, каждая строка SUMPRODUCT матрица равна 1, если каждый столбец содержит значение, которое мы ищем (потому что 1×1×1=1), и 0, если строка недопустима в каждом столбце. Итак, у нас есть список из девяти нулей и единиц, который мы добавляем.

Поскольку это ключевой вопрос," я бы хотел посчитать количество яблок и груш (не бананов), которые бывают зелеными или красными, но не ядовитыми ".

Поэтому я хотел бы предложить решение в формате таблицы соответствует вашим требованиям.

Проверьте снимок экрана:

Запишите эту формулу в E2: =COUNTIFS($A$2:$A$10, "="&$D2,$B$2:$B$10,"="&E$1)

Обратите внимание: заполните ячейку Формулы 1 справа, затем на одну строку вниз, вы получите количество яблок и груш.

Запишите эту формулу в G2: =COUNTIFS($A$2:$A$10, "="&$D2,$C$2:$C$10,"=No")

Заметьте, заполните Формула-1, и вы получите количество неядовитых яблок и груш.

Наконец, суммируйте неядовитую колонку.