Общая формула
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Резюме
Чтобы подсчитать значения в одном списке, которые отсутствуют в другом списке, вы можете использовать формулу, основанную на функциях СЧЁТЕСЛИ и СУММПРОИЗВ.
В показанном примере формула H6 имеет следующий вид:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Что возвращает 1, поскольку значение «Осборн» не появляется в B6: B11.
Объяснение
Функции СЧЁТЕСЛИ проверяют значения в диапазоне на соответствие критериям. Часто предоставляется только один критерий, но в этом случае мы предоставляем более одного критерия.
Для диапазона, мы даем COUNTIF именованный диапазон песни1 (B6: B11), а также критериев, мы предлагаем именованный диапазон List2 (F6: F8).
Поскольку мы даем COUNTIF более одного критерия, мы получаем более одного результата в массиве результатов, который выглядит следующим образом: (2; 1; 0)
Мы хотим подсчитывать только пропущенные значения, которые по определению имеют нулевой счетчик, поэтому мы конвертируем эти значения в ИСТИНА и ЛОЖЬ с помощью оператора «= 0», который дает: (ЛОЖЬ; ЛОЖЬ; ИСТИНА)
Затем мы принудительно устанавливаем значения TRUE FALSE на 1 и 0 с помощью дважды отрицательного оператора (-), который дает: (0; 0; 1)
Наконец, мы используем SUMPRODUCT, чтобы складывать элементы в массиве и возвращать общее количество пропущенных значений.
Альтернатива с MATCH
Если вы предпочитаете более буквальные формулы, вы можете использовать приведенную ниже формулу, основанную на ПОИСКПОЗ, которая буквально подсчитывает значения, которые «отсутствуют» с помощью функции ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
