Общая формула
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Резюме
Чтобы суммировать значения в совпадающих столбцах и строках, вы можете использовать функцию СУММПРОИЗВ. В показанном примере формула J6 выглядит так:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
где данные (C5: G14), дни (B5: B14) и коды (C4: G4) являются именованными диапазонами.
Объяснение
Функция СУММПРОИЗВ может обрабатывать массивы изначально, без необходимости ввода управляющего сдвига.
В этом случае мы умножаем все значения в данных именованного диапазона на два выражения, которые отфильтровывают значения, не представляющие интереса. Первое выражение применяет фильтр на основе кодов:
(codes=J4)
Поскольку J4 содержит «A002», выражение создает массив значений ИСТИНА ЛОЖЬ, например:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Второе выражение фильтрует по дню:
(days=J5)
Поскольку J4 содержит «ср», выражение создает массив ИСТИННЫХ ЛОЖНЫХ значений, например:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
В Excel значения ИСТИНА ЛОЖЬ автоматически приводятся к значениям 1 и 0 с помощью любой математической операции, поэтому операция умножения приводит указанные выше массивы к единицам и нулям и создает 2D-массив с теми же размерами, что и исходные данные. Процесс можно визуализировать, как показано ниже:
Наконец, СУММПРОИЗВ возвращает сумму всех элементов в окончательном массиве 9.
Считайте вместо суммы
Если вы хотите подсчитывать совпадающие значения вместо суммирования, вы можете сократить формулу до:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Обратите внимание, что этот счетчик будет включать пустые ячейки.
Заметки
- Хотя в примере показан только один сопоставленный столбец, эта формула правильно суммирует несколько сопоставленных столбцов.
- Если вам нужно сопоставить только столбцы (а не строки), вы можете использовать такую формулу.
- Чтобы сопоставить только строки, вы можете использовать функцию СЧЁТЕСЛИМН.
