Conditional Count

[Tigerexcel]

I was after a formula that counts providing 2 criteria are met. One is that a code has a 2 in it eg ABC2020, the other is that the score is at least 50. The problem I have is that all the information is in one cell. I can get the first part to work through COUNTIF(range,"<>ABC2*") but not the second part which is the condition that the score is at least 50. Can I do this through COUNTIFS or some other function, or do I need a different approach with some data transformation first?
Data looks like this, with desired result in Count column.

ID no.	Unit1	Unit2	Unit3	Unit4	Count
ID 1	ABC2020 56-P	ABC2040 28-N	ABC1110 50-P	ABC1040 61-C	1
ID 2	ABC2020 36-N	ABC2090 81-HD	ABC2040 66-C		2
ID 3	ABC2020 72-D	ABC2040 28-N	ABC1110 50-P		1

 

[Peter_SSs]

Perhaps you can clarify further exactly what you want as your responses in post 9 & 10 are confusing since Michael's code and my formulas do quite different things.

For example, Michael's code produces one result for the whole range whereas mine produces a result for each row. Further, Michael's code would count "ABC1120 66-P" but in post 5 you said it should be excluded. The code would also count "ABC1110 72-P"

.. there could be single digit results.

Does that mean only single or double digits - nothing longer than that?

.. it returns a value for each ID?

In that case, how/where would the results be presented?


Are you actually looking for a formula or code solution - or you don't care?

 

[Tigerexcel]

Definitely one result for each ID, so one per each row.
These are unit results, they can only range from 0 to 100 however no-one has attained 100 so safe to say 0 to 99.
The solution could be VBA or function-based, whatever I can understand and adapt to my circumstances.

 

[Peter_SSs]

OK, thanks. So the formula already suggested does the job you want?

 

[Michael M]

Each row individually

Sub MM1()
Dim n As Long, cell As Range, ans As Long, lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = 0
For r = 2 To lr
For Each cell In Range("B" & r & ":E" & r) 'change ranges to suit
On Error Resume Next
ans = Mid(cell, 9, 2) * 1
    If InStr(cell, "ABC2") And ans >= 50 Then
        n = n + 1
    End If
    Next cell
Range("F" & r).Value = n
n = 0
Next r
End Sub

 

[Tigerexcel]

Each row individually

Sub MM1()
Dim n As Long, cell As Range, ans As Long, lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
n = 0
For r = 2 To lr
For Each cell In Range("B" & r & ":E" & r) 'change ranges to suit
On Error Resume Next
ans = Mid(cell, 9, 2) * 1
    If InStr(cell, "ABC2") And ans >= 50 Then
        n = n + 1
    End If
    Next cell
Range("F" & r).Value = n
n = 0
Next r
End Sub

Thanks Michael, it works!

 

[Tigerexcel]

OK, thanks. So the formula already suggested does the job you want?

Thanks Peter, works fine and now with 2 methods I can cross-check the results.

 

[Michael M]

Glad we could help ??