Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
Assuming your data is laid out as described, why doesn't =COUNTIF([ValidCount],">0") work? –  dav May 1 '13 at 16:23
    
Those last two columns are just there for the example. In reality I'd have to add them and since the data is dynamic, this would quickly become a PITA. –  Tom Wright May 1 '13 at 16:44
    
the OR function needs a argument following first function not satisfying the criteria. –  Buscar웃 May 1 '13 at 18:07
add comment

3 Answers 3

up vote 2 down vote accepted

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.....

share|improve this answer
    
Woah, that's a monster of a formula! Thanks for sharing! –  Jerry May 1 '13 at 18:35
add comment

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.

share|improve this answer
    
There are blanks in that range and there are no zeros. –  Tom Wright May 1 '13 at 16:45
    
@TomWright I assumed that there were zeroes since your sample table had zeroes? Also, you didn't mention that you added the last two columns in your OP... Could you amend your post please? –  Jerry May 1 '13 at 17:57
    
Sorry, yeah, have made the explanation of the sample table clearer. –  Tom Wright May 1 '13 at 18:00
    
@TomWright How about one column? I don't think there are single formulae to do this without using VBA. I edited my answer. –  Jerry May 1 '13 at 18:03
    
You can do this with single formulas - see my answer –  barry houdini May 1 '13 at 18:31
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.