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]

One is that a code has a 2 in it eg ABC2020,... I can get the first part to work through COUNTIF(range,"<>ABC2*")

How does that count cells that have a 2?

he other is that the score is at least 50.

Perhaps you could define what part of the cell is the "score" since we know nothing of your data or what it represents.

 

[Tigerexcel]

Oops, the formula above should be Countif(range,"=ABC2*").

The score is the number after the unit code, so in ABC2020 56-P, the score would be 56, the -P indicates that it's a Pass and is irrelevant to the exercise so could be removed.

 

[Peter_SSs]

Oops, the formula above should be Countif(range,"=ABC2*").

So, all values start with ABC?
That still would not count ABC1120. Perhaps that is not possible?

 

[Tigerexcel]

All cells start with ABC. ABC1120 should be excluded, may be I'm asking too much of the function.

 

[Michael M]

How about this, notwithstanding a shot of brilliance from @Peter_SSs

Sub MM1()
Dim n As Long, cell As Range, ans As Long
n = 0
For Each cell In Range("B2:E4") "change ranges to suit
 On Error Resume Next
 ans = Mid(cell, 9, 2) * 1
    If InStr(cell, 2) And ans >= 50 Then
        n = n + 1
    End If
    Next cell
MsgBox n
End Sub

 

[Peter_SSs]

All cells start with ABC. ABC1120 should be excluded, may be I'm asking too much of the function.

Just trying to understand as your original description of "has a 2 in it" and your formula part did not seem to agree. It seems the formula part was correct.
Try this. I have assumed all scores are 2 digits as that is the case for all your examples.

20 07 04.xlsm
	A	B	C	D	E	F
1	ID no.	Unit1	Unit2	Unit3	Unit4	Count
2	ID 1	ABC2020 56-P	ABC2040 28-N	ABC1110 50-P	ABC1040 61-C	1
3	ID 2	ABC2020 36-N	ABC2090 81-HD	ABC2040 66-C		2
4	ID 3	ABC2020 72-D	ABC2040 28-N	ABC1110 50-P		1
Count

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(--(LEFT(B2:E2,4)="ABC2"),--(MID(B2:E2&REPT(0,10),9,2)+0>=50))

 

[Peter_SSs]

I have assumed all scores are 2 digits ..

If that assumption is incorrect (eg yellow cells) then

20 07 04.xlsm
	A	B	C	D	E	F
1	ID no.	Unit1	Unit2	Unit3	Unit4	Count
2	ID 1	ABC2020 56-P	ABC2040 28-N	ABC1110 5-P	ABC1040 61-C	1
3	ID 2	ABC2020 36-N	ABC2090 2881-HD	ABC2040 66-C		2
4	ID 3	ABC2020 7-D	ABC2040 528-N	ABC1110 50-P		1
Count 2

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(--(LEFT(B2:E2,4)="ABC2"),--(MID(SUBSTITUTE(SUBSTITUTE(B2:E2&" 0","-"," ")," ",REPT(" ",20)),20,20)+0>=50))

 

[Tigerexcel]

How about this, notwithstanding a shot of brilliance from @Peter_SSs

Sub MM1()
Dim n As Long, cell As Range, ans As Long
n = 0
For Each cell In Range("B2:E4") "change ranges to suit
On Error Resume Next
ans = Mid(cell, 9, 2) * 1
    If InStr(cell, 2) And ans >= 50 Then
        n = n + 1
    End If
    Next cell
MsgBox n
End Sub

Hi Michael,

The macro does return the correct value, can you alter it so that it returns a value for each ID?

 

[Tigerexcel]

If that assumption is incorrect (eg yellow cells) then

20 07 04.xlsm
	A	B	C	D	E	F
1	ID no.	Unit1	Unit2	Unit3	Unit4	Count
2	ID 1	ABC2020 56-P	ABC2040 28-N	ABC1110 5-P	ABC1040 61-C	1
3	ID 2	ABC2020 36-N	ABC2090 2881-HD	ABC2040 66-C		2
4	ID 3	ABC2020 7-D	ABC2040 528-N	ABC1110 50-P		1
Count 2

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(--(LEFT(B2:E2,4)="ABC2"),--(MID(SUBSTITUTE(SUBSTITUTE(B2:E2&" 0","-"," ")," ",REPT(" ",20)),20,20)+0>=50))

Thanks Peter, yes your assumption is correct in that there could be single digit results. That is an enormous formula! I had hoped that the humble Countif function might have been able to deal with this situation.