Conditional Count

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
489
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.Unit1Unit2Unit3Unit4Count
ID 1ABC2020 56-PABC2040 28-NABC1110 50-PABC1040 61-C1
ID 2ABC2020 36-NABC2090 81-HDABC2040 66-C2
ID 3ABC2020 72-DABC2040 28-NABC1110 50-P1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
489
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
OK, thanks. So the formula already suggested does the job you want?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Each row individually
VBA Code:
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

Active Member
Joined
Mar 6, 2020
Messages
489
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Each row individually
VBA Code:
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!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad we could help 🖒🖒
 

Watch MrExcel Video

Forum statistics

Threads
1,123,241
Messages
5,600,495
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top