Общая формула
=SUMPRODUCT(COUNTIF(range1,range2))
Резюме
Чтобы сравнить два диапазона и подсчитать общее количество совпадений между двумя диапазонами, можно использовать формулу, которая объединяет функцию СЧЁТЕСЛИ с функцией СУММПРОИЗВ. В показанном примере формула в ячейке G7:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Примечание: эта формула не заботится о расположении или порядке элементов в каждом диапазоне.
Объяснение
Предположим, у вас есть какой-то «главный» список, а также есть другой список, содержащий некоторые из тех же элементов. Вам нужна формула, которая сравнивает значения во втором списке, чтобы увидеть, сколько из них отображается в первом списке. Вам не важен порядок, в котором элементы, вы просто хотите знать, сколько элементов в списке 2 отображается в списке 1.
Функция СЧЁТЕСЛИ будет подсчитывать вещи в диапазоне, соответствующем вашим критериям. Обычно вы задаете COUNTIF диапазон, например A1: A10, и простой критерий, например «> 10». СЧЁТЕСЛИ тогда вернет количество ячеек в A1: A10, которые больше 10.
Однако в этом случае мы даем СЧЁТЕСЛИ диапазон критериев. Мы не используем никаких логических операторов, что означает, что СЧЁТЕСЛИ будет проверять эквивалентность (т. Е. Ведет себя так, как если бы мы использовали оператор равенства (=)).
Поскольку мы даем COUNTIF диапазон (также называемый «массивом»), содержащий 7 элементов, COUNTIF в результате вернет массив из 7 элементов. Каждый элемент в массиве результатов представляет собой счетчик. В этом примере этот массив, который возвращает COUNTIF, выглядит так:
(1; 1; 0; 1; 1; 0; 0)
Теперь нам просто нужно сложить элементы в этом массиве, что идеально подходит для SUMPRODUCT. Функция СУММПРОИЗВ - это универсальная функция, которая изначально обрабатывает массивы без какого-либо специального синтаксиса массива.
Если вы передадите SUMPRODUCT два или более массива, он умножит массивы вместе, суммирует результаты и вернет это число. В этом случае мы передаем СУММПРОИЗВ только один массив, поэтому он просто суммирует элементы в массиве и возвращает 4 в качестве результата.
Совпадение по строкам
Если вы хотите сравнить два диапазона или столбца и хотите подсчитывать совпадения на уровне строки (т.е. подсчитывать совпадения только тогда, когда один и тот же элемент появляется в той же позиции), вам понадобится другая формула.
