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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

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

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

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

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

ADVERTISEMENT

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,766
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
How about this, notwithstanding a shot of brilliance from @Peter_SSs
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEF
1ID no.Unit1Unit2Unit3Unit4Count
2ID 1ABC2020 56-PABC2040 28-NABC1110 50-PABC1040 61-C1
3ID 2ABC2020 36-NABC2090 81-HDABC2040 66-C2
4ID 3ABC2020 72-DABC2040 28-NABC1110 50-P1
Count
Cell Formulas
RangeFormula
F2:F4F2=SUMPRODUCT(--(LEFT(B2:E2,4)="ABC2"),--(MID(B2:E2&REPT(0,10),9,2)+0>=50))
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I have assumed all scores are 2 digits ..
If that assumption is incorrect (eg yellow cells) then

20 07 04.xlsm
ABCDEF
1ID no.Unit1Unit2Unit3Unit4Count
2ID 1ABC2020 56-PABC2040 28-NABC1110 5-PABC1040 61-C1
3ID 2ABC2020 36-NABC2090 2881-HDABC2040 66-C2
4ID 3ABC2020 7-DABC2040 528-NABC1110 50-P1
Count 2
Cell Formulas
RangeFormula
F2:F4F2=SUMPRODUCT(--(LEFT(B2:E2,4)="ABC2"),--(MID(SUBSTITUTE(SUBSTITUTE(B2:E2&" 0","-"," ")," ",REPT(" ",20)),20,20)+0>=50))
 

Tigerexcel

Active Member
Joined
Mar 6, 2020
Messages
489
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
How about this, notwithstanding a shot of brilliance from @Peter_SSs
VBA Code:
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

Active Member
Joined
Mar 6, 2020
Messages
489
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If that assumption is incorrect (eg yellow cells) then

20 07 04.xlsm
ABCDEF
1ID no.Unit1Unit2Unit3Unit4Count
2ID 1ABC2020 56-PABC2040 28-NABC1110 5-PABC1040 61-C1
3ID 2ABC2020 36-NABC2090 2881-HDABC2040 66-C2
4ID 3ABC2020 7-DABC2040 528-NABC1110 50-P1
Count 2
Cell Formulas
RangeFormula
F2:F4F2=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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