Общая формула
=SUMPRODUCT(SUMIF(codes,lookups,values))
Резюме
Чтобы суммировать значения, полученные с помощью операции поиска, вы можете использовать СУММПРОИЗВ с функцией СУММЕСЛИ.
В показанном примере формула H5 имеет следующий вид:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Где коды - это именованный диапазон J4: J5, а значения - именованный диапазон K4: K5.
Контекст
Иногда вам может потребоваться суммировать несколько значений, полученных с помощью операции поиска. В этом примере мы хотим суммировать выходные дни, проводимые каждую неделю, на основе системы кодов, где F = полный день, а H = полдня. Если день пуст, время не учитывалось.
Задача состоит в том, чтобы найти формулу, которая одновременно находит и суммирует значения, связанные с F и H.
Объяснение
Ядром этой формулы является СУММЕСЛИ, которая используется для поиска правильных значений для F и H. Использование СУММЕСЛИ для поиска значений является более продвинутым методом, который хорошо работает, когда значения являются числовыми и в "поисковой таблице" нет дубликатов. ".
Уловка в этом случае заключается в том, что критерием СУММЕСЛИ является не одно значение, а массив значений в диапазоне C5: G5:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Поскольку мы задаем СУММЕСЛИ более одного критерия, СУММЕСЛИ вернет более одного результата. В показанном примере результатом СУММЕСЛИ является следующий массив:
(1,0,5,0,0,0)
Обратите внимание, что мы правильно получаем 1 для каждого «F» и 0,5 для каждого «H»., А пустые значения за неделю генерируют ноль.
Наконец, мы используем SUMPRODUCT для суммирования значений в массиве, возвращаемом SUMIF. Поскольку существует только один массив, СУММПРОИЗВ просто возвращает сумму всех значений.
