Включить условие ИЛИ в COUNTIF?

Question

Этот вопрос задавался ранее, и, кажется, ответ всегда складывается из двух COUNTIF . Это хорошо для большинства приложений, но не будет работать для меня.

У меня есть столбец, который может содержать 0, 1 или более действительных токенов, а также 0, 1 или более недействительных токенов. Например, если A, B и C являются действительными токенами, а D, E и F являются недействительными, у меня могут быть данные, которые выглядят как в столбце "Token" в таблице ниже. Я добавил столбцы "ValidCount" и "Valid", чтобы показать разные интерпретации.

    ╔══════╦═══════╦════════════╦═══════╗
    ║ Row# ║ Tokens║ ValidCount ║ Valid ║
    ╠══════╬═══════╬════════════╬═══════╣
    ║   1  ║ A, E  ║        1   ║ True  ║
    ║   2  ║       ║        0   ║ False ║
    ║   3  ║ B     ║        1   ║ True  ║
    ║   4  ║ F, G  ║        0   ║ False ║
    ║   5  ║ A,B,C ║        3   ║ True  ║
    ║   6  ║ C     ║        1   ║ True  ║
    ╚══════╩═══════╩════════════╩═══════╝

Я могу сосчитать экземпляры действительных токенов с помощью следующего (где необработанные данные находятся на листе с именем "Raw", токены находятся в столбце AD, а соответствующий токен находится в ячейке A30 на листе с именем "Summary"):

=COUNTIF(Raw!$AD$2:$AD$79,CONCATENATE("*",Summary!$A30,"*"))

Проблема в том, что если я делаю это для каждого действующего токена и суммирую их, я в итоге считаю общее количество действительных токенов. Я хочу получить количество ячеек, по крайней мере, с одним действительным токеном. Используя приведенный выше пример, правильный ответ - 4, а не 6.

То, что я хочу сделать, это использовать функцию OR как это:

=COUNTIF(Raw!$AD$2:$AD$79,OR(CONCATENATE("*",Summary!$A30,"*"),CONCATENATE("*",Summary!$A31,"*"),[...snip...]))

но это дает 0.

Есть ли способ сделать это? В идеале не прибегая к VBA.

Answer 1

Вот два возможных подхода к формуле - если в ячейках отображаются "действительные токены" (по одному).A30:A32, то вы можете использовать эту версию

=SUMPRODUCT((ISNUMBER(SEARCH(Summary!A30,Raw!$AD$2:$AD$79))+ISNUMBER(SEARCH(Summary!A31,Raw!$AD$2:$AD$79))+ISNUMBER(SEARCH(Summary!A32,Raw!$AD$2:$AD$79))>0)+0)

или более легко "расширяемый" для большего диапазона критериев попробуйте эту формулу массива

=SUM((MMULT(ISNUMBER(SEARCH(TRANSPOSE(Summary!A30:A32),Raw!$AD$2:$AD$79))+0,{1;1;1})>0)+0)

подтверждено с помощью CTRL+SHIFT+ENTER

{1; 1; 1} должен соответствовать длине диапазона критериев, а TRANSPOSE требуется, только если диапазон критериев вертикальный .....

Answer 2

Вы могли бы найти все нули и вычесть это из общей суммы?

Я думал о:

=counta(Raw!$AD$2:$AD$79)-countif(Raw!$AD$2:$AD$79,0)

[Я предполагаю, что в диапазоне AD2:AD79 нет пустых ячеек]

Кажется, это дает мне то, что ты хочешь получить. Не могли бы вы подтвердить?

EDIT: Хорошо, кажется , ваши первоначальные данные не все , что вы на самом деле есть, так, изменение в подходе.

Я бы предложил эту формулу в одном дополнительном столбце (ячейка C2):

=IF(AND(ISERROR(FIND("A",B2)),ISERROR(FIND("B",B2)),ISERROR(FIND("C",B2))),0,1)

Это дает 0 если какой-либо из A, B или C не находится в ячейке B2 , и дает 1 если любой из них находится в ячейке B2 .

Перетащите на дно. В строке состояния Excel отображается сумма, или вы можете использовать функцию =SUM() для столбца.

Answer 3

Моя первая мысль была с помощью функции COUNTIFS но ANDs критерии вместе. OR - правильная идея, но, как вы заметили, она не проверяет значение ячейки внутри критериев ИЛИ.

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

Sub CountTokens()

    Dim count As Integer
    Dim token As String
    Dim tokens As Variant

    For Each Cell In Sheet1.Range("A1:A6")
        tokens = Split(Cell.Value, ", ")
        For tIndex = LBound(tokens) To UBound(tokens)
            token = tokens(tIndex)
            If token = "A" Or token = "B" Or token = "C" Then
                count = count + 1
                Exit For
            End If
        Next tIndex
    Next Cell

    MsgBox "Count: " & count

End Sub