# Include an OR condition in a COUNTIF?

This question has been asked before and the answer always seems to be to add together two `COUNTIF`s. 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.

• 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. May 1, 2013 at 16:44
• the OR function needs a argument following first function not satisfying the criteria. May 1, 2013 at 18:07

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

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

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.

• There are blanks in that range and there are no zeros. 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? May 1, 2013 at 17:57
• Sorry, yeah, have made the explanation of the sample table clearer. 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. 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
```