3

This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me.

I have a column that can contain 0, 1 or more valid tokens as well as 0, 1 or more invalid tokens. For example, if A, B and C are valid tokens and D, E and F are invalid, I might have data that looks like in the "Token" column in the table below. I've added the "ValidCount" and "Valid" columns to show the different interpretations.

    ╔══════╦═══════╦════════════╦═══════╗
    ║ 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  ║
    ╚══════╩═══════╩════════════╩═══════╝

I can count instances of valid tokens with the following (where the raw data is on a sheet called "Raw", the tokens are in column AD and the token in question is in cell A30 on the sheet called "Summary":

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

The trouble is that if I do this for each valid token and sum them, I end up counting the total number of valid tokens. What I want to do is get the number of cells with at least one valid token. Using the example above, the correct answer is 4, not 6.

What I want to do is use an OR function like this:

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

but this gives 0.

Is there a way to do this? Ideally without resorting to VBA.

Tom Wright
  • 553

3 Answers3

3

Here are two possible formula approaches - if the "valid tokens" are shown (one each) in cells Summary!A30:A32 then you can use this version

=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)

or more easily "extensible" for a larger criteria range try this array formula

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

confirmed with CTRL+SHIFT+ENTER

{1;1;1} needs to match the length of the criteria range and TRANSPOSE is only required if the criteria range is vertical.....

barry houdini
  • 11,212
1

You could maybe find all the zeroes, and subtract this from the total?

I'm thinking of:

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

[I'm assuming there are no blank cells in range AD2:AD79]

That seems to get me what you want to get. Could you perhaps confirm?

EDIT: Okay, seems your initial data is not at ALL what you actually have, so, change in approach.

I would suggest this formula in one additional column (cell C2):

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

This gives 0 if any of A, B or C aren't in cell B2, and gives 1 if any one of those are in cell B2.

Drag to the bottom. Excel's status bar gives you the sum, or you could use a =SUM() function on the column.

Jerry
  • 4,956
0

My first thought was using the COUNTIFS function but that ANDs the criteria together. The OR is the right idea but as you noticed it doesn't test the cell value inside the OR criteria.

The only thing I could come up with was to use VBA. I put your sample tokens in the first six rows in a worksheet and came up with this code. It could easily be modified to be a new function to use as a formula as well. 

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
Brad Patton
  • 10,668