Загальна формула
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Резюме
Для підрахунку значень в одному списку, яких немає в іншому списку, ви можете використовувати формулу на основі функцій COUNTIF і SUMPRODUCT.
У наведеному прикладі формула в H6 така:
=SUMPRODUCT(--(COUNTIF(list1,list2)=0))
Що повертає 1, оскільки значення "Осборн" не відображається в B6: B11.
Пояснення
Функції COUNTIF перевіряють значення в діапазоні на відповідність критеріям. Часто подається лише один критерій, але в цьому випадку ми надаємо більше одного критерію.
Для діапазону, ми даємо COUNTIF іменований діапазон песні1 (B6: B11), а також критеріїв, ми пропонуємо іменований діапазон List2 (F6: F8).
Оскільки ми даємо COUNTIF більше одного критерію, ми отримуємо більше одного результату в масиві результатів, який виглядає так: (2; 1; 0)
Ми хочемо підрахувати лише ті значення, які відсутні, які за визначенням мають нульовий рахунок, тому ми перетворюємо ці значення в TRUE і FALSE за допомогою оператора "= 0", що дає: (FALSE; FALSE; TRUE)
Потім ми примушуємо значення TRUE FALSE до 1s і 0s за допомогою подвійного негативного оператора (-), який видає: (0; 0; 1)
Нарешті, ми використовуємо SUMPRODUCT для складання елементів у масиві та повернення загальної кількості відсутніх значень.
Альтернатива з MATCH
Якщо ви віддаєте перевагу більш буквальним формулам, ви можете використати формулу нижче, засновану на MATCH, яка буквально підраховує значення, які "відсутні" за допомогою функції ISNA:
=SUMPRODUCT(--ISNA(MATCH(list2,list1,0)))
