Excel - считать, если в ячейке несколько слов

Вы можете попробовать это, используя wildcards* с TEXTBEFORE() и TEXTAFTER()

• Формула, используемая в ячейке D2

=COUNTIFS(A1:A10,"*"&TEXTBEFORE(C2,",")&"*",A1:A10,"*"&TEXTAFTER(C2,",")&"*")

РЕДАКТИРОВАТЬ

Как предложил Том Шарп Сэр,

• Формула, используемая в ячейке D2

=LET(x,TEXTSPLIT(C2,","),y,COUNTA(x),
SUM(--(MMULT(N(ISNUMBER(SEARCH(x,$A$1:$A$10))),SEQUENCE(y,,1,0))=y)))

Ответ был

COUNTIFS($A:$A,"*"&$C$2&"*",$A:$A,"*"&$C$3&"*")

С2 = АА С3 = ББ

Как насчет:

=LET(searchvalues,  C2,
     data,          $A$1:$A$10,
     split,         TEXTSPLIT(searchvalues,","),
     count,         COLUMNS(split),
SUM(
    --(MMULT(
             --ISNUMBER(SEARCH(split,data)),
             SEQUENCE(count,,1,0))
       =count)))

Он разбивает значения, которые вы хотите найти (хранящиеся в C2 в моем примере), на отдельные значения: split

Чем split ищется в каждой строке вашего data. Это возвращает TRUE или FALSE, завернутые в --, изменяет TRUE на 1 и FALSE на 0. Это используется в MMULT и возвращает сумму вхождений каждого отдельного значения split. Наконец, результат MMULT должен быть равен количеству разделенных значений поиска. Сумма этого факта является конечным результатом.

Следующий подход рассматривает переменное количество слов для поиска, а не только 2 слов, как во входном образце:

=LET(lk, B1, A, $A$1:$A$11, lks, TEXTSPLIT(lk,, ","),
  byr, BYROW(A, LAMBDA(x,SUM(COUNTIF(x,"*"&lks&"*")))), SUM(N(byr=ROWS(lks))))

У него есть оговорка: если слово повторяется более одного раза в строке в первом столбце, оно считается как 1, как вы можете видеть в следующем выводе. Это связано с тем, как COUNTIF (или COUNTIFS) работает с подстановочными знаками (другие ответы, предоставленные с использованием SEARCH, дают тот же результат). Если с этим предположением все в порядке, то оно работает. Осталось просто перетащить формулу вниз (formula1).

ОБНОВЛЕНИЕ: Учитывая комментарий @JvdV, чтобы избежать ложных срабатываний, когда искомое слово может быть подстрокой столбца A в данной строке. Как, например, поиск слова AA в строке: AAA приведет к ложному срабатыванию. Следующая версия избегает этого:

=LET(lk, B1, A, $A$1:$A$11, lks, TEXTSPLIT(lk,, ","),
 byr, BYROW(A, LAMBDA(x, SUM(N(TEXTSPLIT(x,",") = lks)))), SUM(N(byr=ROWS(lks))))

Вот результат:

Я намеренно добавил выделенные случаи, чтобы проверить дополнительные ситуации. Строка 11 повторяет слово: AA и BB, но в конечном результате считается как 1.

Следующий подход пытается определить общее количество отсчетов с учетом повторений слова в столбце A (формула 2):

=LET(lk, B1, A, $A$1:$A$11, lks, TEXTSPLIT(lk,, ","),
 byr, BYROW(A, LAMBDA(x, LET(match, IFERROR(TOCOL(XMATCH(TEXTSPLIT(x, ","),
 lks),2),0), ux, UNIQUE(match), IF(ROWS(ux) < ROWS(lks), 0,
   MIN(MMULT(TRANSPOSE(N(match = TOROW(ux))), SEQUENCE(ROWS(match),,1,0))))))),
 SUM(byr))

Теперь мы получаем следующий результат:

Теперь мы получаем дополнительный счет для случая AA, BB.

В формуле2 match имеет позицию индекса в результате вызова XMATCH, мы используем TOCOL для удаления ненайденных значений #N/A. Если слова не найдены, мы используем IFERROR, чтобы присвоить нулевое значение. Условие IF:

IF(ROWS(ux) < ROWS(lks)

Обеспечивает вычисление ненулевых значений только в том случае, если все слова были найдены. Мы используем MMULT для вычисления количества повторений искомых слов для каждой строки. Мы берем минимум, чтобы гарантировать, что мы рассматриваем только сценарии, в которых присутствуют все слова. Может возникнуть ситуация, когда одно из искомых слов имеет больше значений, чем другое, поэтому мы берем минимум. Поэтому мы подсчитываем весь набор поисковых слов, найденных независимо от порядка,

Вы можете попробовать:

Формула в D2:

=MAP(C2:C5,LAMBDA(z,SUM(MAP(A1:A11,LAMBDA(x,LET(y,TEXTSPLIT(z,,","),N(SUM(N(TEXTSPLIT(x,",")=y))=ROWS(y))))))))

Примечание. Мой ответ предполагает отсутствие дубликатов значений в столбце A:A. например: AA,AA.