Загальна формула
=SUMPRODUCT(COUNTIF(range1,range2))
Резюме
Щоб порівняти два діапазони та підрахувати загальну кількість збігів між двома діапазонами, ви можете скористатися формулою, яка поєднує функцію COUNTIF з функцією SUMPRODUCT. У наведеному прикладі формула в комірці G7 має вигляд:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Примітка: ця формула не має значення для розташування чи порядку елементів у кожному діапазоні.
Пояснення
Припустимо, у вас є якийсь "головний" список, а також є інший список, який містить ті самі елементи. Вам потрібна формула, яка порівнює значення у 2-му списку, щоб побачити, скільки їх у першому списку. Вас не хвилює порядок, коли елементи, ви просто хочете знати, скільки елементів у списку 2 з’являється в списку 1.
Функція COUNTIF буде рахувати речі в діапазоні, що відповідає вашим критеріям. Зазвичай ви даєте COUNTIF такий діапазон, як A1: A10, і прості критерії, наприклад "> 10". Потім COUNTIF повертає кількість клітинок у A1: A10, що перевищує 10.
Однак у цьому випадку ми надаємо COUNTIF діапазон для критеріїв. Ми не використовуємо жодних логічних операторів, що означає, що COUNTIF перевірятиме еквівалентність (тобто він поводиться так, ніби ми використовували оператор equals (=)).
Оскільки ми надаємо COUNTIF діапазон (також званий "масив"), який містить 7 елементів, у результаті COUNTIF поверне масив із 7 елементів. Кожен елемент у масиві результатів представляє рахунок. У прикладі цей масив, який повертає COUNTIF, виглядає так:
(1; 1; 0; 1; 1; 0; 0)
Тепер нам просто потрібно скласти елементи в цьому масиві, що є ідеальною роботою для SUMPRODUCT. Функція SUMPRODUCT - це універсальна функція, яка в основному обробляє масиви без особливого синтаксису масиву.
Якщо ви надаєте SUMPRODUCT два або більше масивів, він буде множити масиви разом, підбивати результати та повертати це число. У цьому випадку ми надаємо SUMPRODUCT лише один масив, тому він просто підсумовує елементи масиву і повертає 4 як результат.
Збіг між рядами
Якщо ви хочете порівняти два діапазони або стовпці і хочете підрахувати збіги на рівні рядка (тобто підраховувати збіги лише тоді, коли один і той же елемент виявляється в тій самій позиції), вам знадобиться інша формула.
