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":


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:


but this gives 0.

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

  • 1
    Assuming your data is laid out as described, why doesn't =COUNTIF([ValidCount],">0") work?
    – dav
    May 1, 2013 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, 2013 at 16:44
  • the OR function needs a argument following first function not satisfying the criteria.
    – Ruskes
    May 1, 2013 at 18:07

3 Answers 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


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


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

  • Woah, that's a monster of a formula! Thanks for sharing!
    – Jerry
    May 1, 2013 at 18:35

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

I'm thinking of:


[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):


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.

  • There are blanks in that range and there are no zeros.
    – Tom Wright
    May 1, 2013 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, 2013 at 17:57
  • Sorry, yeah, have made the explanation of the sample table clearer.
    – Tom Wright
    May 1, 2013 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, 2013 at 18:03
  • You can do this with single formulas - see my answer May 1, 2013 at 18:31

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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