Как суммировать значения в диапазоне, если критерии должны иметь несколько возможностей?

Question

Смотрите скриншот. То, что я пытаюсь сделать, это подытожить все плоды, распространяемые в США, но есть несколько плодов, которые мне нужно найти из ключа. Я хочу сделать динамическую формулу вместо суммирования всех отдельных ячеек.

Я предполагаю, что мне нужно использовать SUMIF, но я не смог заставить его работать должным образом. Я пытался объединить SUMIF с INDEX MATCH безрезультатно. В случае США под двумя фруктами функция INDEX MATCH будет подбирать только первый фрукт и искать его в таблице.

Answer 1

В таблице "Ключ" пометьте столбец "Фрукты" уникальным именем. Затем я создал именованные диапазоны на основе заголовков столбцов - в этом нет необходимости, их можно заменить абсолютными адресами. Я предпочел бы использовать "Таблицы" со структурированными ссылками, так как они будут автоматически настраиваться, если вы добавляете или вычитаете строки.

Следующая формула массива должна быть введена, удерживая Ctrl+Shift при нажатии Enter

B14:  =SUM(SUMIF(Fruit, IF(Country=A14,KeyFruit,""),Fruits_Picked))

Заполнение до B16

Объяснение:

• Функция IF вернет массив KeyFruit или null в зависимости от того, соответствует ли страна
  • Например: {"Apples","","","Bananas"}
• Этот массив затем используется в качестве аргумента criteria в SUMIF .
• SUMIF , с аргументом массива для criteria , вернет массив совпадающих значений
• который мы затем складываем с SUM