Count cells that contain at least one of 3 text strings

[Kimelea]

At my wits' end - help gratefully received!

I have a spreadsheet of animal behaviours where each row represents a time period. Each cell contains a letter code for behaviours they performed in that period. There can be more than one behaviour in a single cell. So this would be an example of my column:

A
A B
X

C

B X

Those blank spaces are blank cells, because the animal did none of those behaviours in that period.

I need my formula to count the frequency of particular behaviours. In this example, I want my formula to count cells that contain "A" and/or "B" and/or "C", regardless of the rest of the cell's content, but ignore cells that don't. So the result should be 4. The cell containing "B X" should be counted, but the cell containing "X" only should not. The cell containing "A B" must only be counted once, preventing me from summing separate countif formulas for each behaviour.

It seems so simple - "count cells in a single range that meet at least one of these three criteria". But I can't suss it out after hours of searching and trying.

 

[alansidman]

Here is a VBA solution. In this solution, I assumed that the data was contained in Column A beginning in the first row. Amend the code if this needs to change.

Option Explicit


Sub Behavior()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Long
    x = 0
    For i = 1 To lr
        If InStr(Range("A" & i), "A") > 0 Or InStr(Range("A" & i), "B") > 0 Or InStr(Range("A" & i), "C") > 0 Then
            x = x + 1
        End If
    Next i
    MsgBox ("Behavior Count is " & x)


End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Tetra201]

Try this:

=SUMPRODUCT(SIGN(MMULT(--ISNUMBER(FIND({"A","B","C"},A1:A7)),{1;1;1})))

 

[Kimelea]

OK, in my book you are both wizards who said some magic words and made the things happen.

Seriously, thanks so much. I tried out the formula and instant success - now I can roll it out over a vast load of behaviour observations. (And try to learn from all this by figuring out how that formula works!) I'm extremely grateful for the VBA code as well, though I didn't need to try it in the end.

 

[Tetra201]

Glad to have helped.