Подсчет специальных значений из Sumproduct (countif)

Некоторое время я ухаживал за этим местом, и до сих пор мне удалось найти ответ практически на любую проблему, с которой я столкнулся. Я чувствую, что я нахожусь на правильном пути для ответа, который мне нужен, но я не могу понять, как правильно это сделать.

Ниже у меня есть скриншот упрощенного примера того, что я хочу делать. В этом примере у меня есть 4 человека, которые выбрали 5 лучших любимых цветов (я ужасен в создании поддельных данных, например, так что, пожалуйста, извините варианты цвета haha). В конечном счете, я хочу знать, сколько человек соответствует всем 5 цветам (не обязательно в одном порядке), сколько соответствует 4, 3, 2 и т. Д. Справа от набора данных идеально, как бы я хотел мои данные посмотреть (H2: M11).

Я подошел к этому несколько способов, но не имел большого успеха. Два метода, которые кажутся наиболее близкими:

СМОТРИТЕ НЕИСПРАВНОСТЬ ИДЕИ 1) Я хотел написать формулу, в которой в основном говорилось «подсчитывать количество раз, что sumproduct (countif (Цвета от лица 2,3 и 4: Цвета от лица 1)) равно 5 (в этом примере ноль ). Я бы воспроизвел формулу для 4 совпадающих цветов, 3, 2 и т. Д. Затем просто суммируем и усредняем.

Сама формула ошибочна и возвращается как ошибка. Итак, так как это не вышло …

SEE FAILED IDEA 2) Я подумал о создании таблицы стиля «тепловая карта», в которой у меня могли бы быть листы с 1 по 4 по строкам и столбцам, а затем делать то, что я знаю, что работал как sumproduct (count (B4: F4, B5: F5)) для Лицо 1 против Лица 2 (ответ 3). Единственное отличие состоит в том, что мне нужно будет использовать индекс (совпадение) для определения правильной строки с использованием имени столбца (см. Пример). Перетащите все 16 (4×4) ячейки, а затем просто получите счет (F19: I22, «5»), чтобы подсчитать количество каждого. К сожалению, использование индекса (совпадения) в пределах countif вызывает #REF!

Упрощенные данные примера

Если на самом деле можно заставить любую концепцию работать, я могу манипулировать остальными. Я знаю, что могу просто сделать дизайн карты тепла и вручную изменить формулы для каждого столбца, но мои реальные данные имеют длину более 100 строк. Альтернативы VBA также приветствуются.

Прошу прощения, если мое описание было не очень ясным. Если это сбивает с толку, просто дайте мне знать, и я постараюсь быть более ясным. БЛАГОДАРЯ!

Переход с неудачной идеей № 2. Используйте эту формулу массива для шага 1:

=SUM(COUNTIF(INDEX($B:$F,MATCH(J$2,$A:$A,0),0),INDEX($B:$F,MATCH($I3,$A:$A,0),0))) 

Будучи формулой массива, она должна быть подтверждена клавишей Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит {} вокруг формулы.

введите описание изображения здесь

Шаг 2:

 =COUNTIFS($J3:$M3,P$2,$J$2:$M$2,"<>" & $O3) 

введите описание изображения здесь

  • Разрешить выбор «Все» или «пустой» выбор
  • Excel - COUNTIFS с несколькими критериями диапазона дат
  • Интеллектуальные счетчики Excel со встроенными if
  • Excel подсчитывает количество раз, когда значение появляется в группе столбцов, но учитывает только один экземпляр имени для строки
  • Формула Excel - частичное совпадение и значение показа
  • Функция Excel MATCH + COUNTIF: получение n-го значения в массиве
  • Как подсчитать имя, если степень 80 или выше
  • COUNTIF Partial Match
  • Множественные критерии Excel для сохранения строк, приоритетных при дедупликации - макрос VBA или другой метод
  • Сделать COUNTIFS игнорировать пустые ячейки
  • Подсчет дублирующих данных, которые удовлетворяют условиям и удаляют данные
  • Давайте будем гением компьютера.