# Excel formula count occurances

#### Biz

##### Well-known Member
Dear All,

I'm trying to construct formulas in Col G which matches Col H.
My criteria to add matches in a string is located in cell M2.

Your help would be greatly appreciated.

Kind Regards

Biz

Excel 2013 32 bit
BCDEFGHIJKLM
1
NameCategoryMonthStringCalShould BeCriteria
2
TomRespite
1/11/2016​
Respond,Other,Reassessed
0​
3​
Respond,Other,Reassessed
3
TomOther
1/11/2016​
Respond,Other,Reassessed
0​
3​
4
TomReassessed
1/11/2016​
Respond,Other,Reassessed
0​
3​
5
BKRespite
1/12/2016​
Respond
1​
1​
6
TomOther
1/12/2016​
Other,Other
0​
2​
7
TomOther
1/12/2016​
Other,Other
0​
2​
8
SpiderA
1/12/2016​
A
0​
0​

<tbody>
</tbody>
 Sheet: Sheet2

<tbody>
</tbody>

Excel 2013 32 bit
BCDEFGHIJKLM
1NameCategoryMonthStringCalShould BeCriteria
2TomRespite1/11/2016Respond,Other,Reassessed03Respond,Other,Reassessed
3TomOther1/11/2016Respond,Other,Reassessed03
4TomReassessed1/11/2016Respond,Other,Reassessed03
5BKRespite1/12/2016Respond11
6TomOther1/12/2016Other,Other02
7TomOther1/12/2016Other,Other02
8SpiderA1/12/2016A00

</tbody>
Sheet2

Worksheet Formulas
CellFormula
G2= SUMPRODUCT(--ISNUMBER( SEARCH("," & E2 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))
G3= SUMPRODUCT(--ISNUMBER( SEARCH("," & E3 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))
G4= SUMPRODUCT(--ISNUMBER( SEARCH("," & E4 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))
G5= SUMPRODUCT(--ISNUMBER( SEARCH("," & E5 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))
G6= SUMPRODUCT(--ISNUMBER( SEARCH("," & E6 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))
G7= SUMPRODUCT(--ISNUMBER( SEARCH("," & E7 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))
G8= SUMPRODUCT(--ISNUMBER( SEARCH("," & E8 & ",","," &SUBSTITUTE(TRIM(\$M\$2),",",", ") & ",")))

</tbody>

<tbody>
</tbody>

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### AliGW

##### Banned
Based on the sample data provided, try this in column H copied down:

=COUNTIFS(\$B\$2:\$B\$8,B2,\$D\$2:\$D\$8,D2)

#### Biz

##### Well-known Member
Based on the sample data provided, try this in column H copied down:

=COUNTIFS(\$B\$2:\$B\$8,B2,\$D\$2:\$D\$8,D2)

It's not the correct formula because it does not refer to criteria in cell M2.
Formula should compare a cell and then count occurrences based on criteria (Cell M2).

Hope it make sense.

Biz

#### Biz

##### Well-known Member
Hi,

Derived my solution using Excel UDF. Wandering if there is native formula solution without using arrays Ctrl+Shift+Enter.
Prefer my original setup as per first post where cell M2 (the criteria), but had to break up it up, to make Excel UDF work.
If there's a formula solution I'm still interested and even Excel UDF would work with original set up, cell M2.

<title>Excel Jeanie HTML</title>******>

<!-- ######### Start Created Html Code To Copy ########## -->

Sheet2

 * B C D E F G H I J K L M 1 Name Category Month String * Cal Should Be * * * * Criteria 2 Tom Respite 01/11/16 Respond,Other,Reassessed * 3 3 * * * * Respond 3 Tom Other 01/11/16 Respond,Other,Reassessed * 3 3 * * * * Other 4 Tom Reassessed 01/11/16 Respond,Other,Reassessed * 3 3 * * * * Reassessed 5 BK Respite 01/12/16 Respond * 1 1 * * * * * 6 Tom Other 01/12/16 Other,Other * 2 2 * * * * * 7 Tom Other 01/12/16 Other,Other * 2 2 * * * * * 8 Spider A 01/12/16 A * 0 0 * * * * *

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 68px;"><col style="width: 180px;"><col style="width: 9px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 9px;"><col style="width: 9px;"><col style="width: 9px;"><col style="width: 9px;"><col style="width: 180px;"></colgroup><tbody>
</tbody>

 Cell Formula G2 =CountOccurencesBK(E2,\$M\$2:\$M\$4) G3 =CountOccurencesBK(E3,\$M\$2:\$M\$4) G4 =CountOccurencesBK(E4,\$M\$2:\$M\$4) G5 =CountOccurencesBK(E5,\$M\$2:\$M\$4) G6 =CountOccurencesBK(E6,\$M\$2:\$M\$4) G7 =CountOccurencesBK(E7,\$M\$2:\$M\$4) G8 =CountOccurencesBK(E8,\$M\$2:\$M\$4)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

<!-- ######### End Created Html Code To Copy ########## -->

Code:
Function CountOccurencesBK(rngCount As Range, rngCriteria As Range)
Dim myArrayCount, myArrayCriteria, bExistInArray As Boolean
Dim i As Long, j As Long, iFound As Long
Application.Volatile
'~~> Default Flag set to zero
iFound = 0
bExistInArray = False
'~~> Populate Arrays
myArrayCount = Split(rngCount, ",")
myArrayCriteria = rngCriteria.Value
'~~> Loop to Lookup value array aka myArrayCount
For i = LBound(myArrayCount) To UBound(myArrayCount)
'~~> Returns if item exits as True or False
bExistInArray = IsInArray(CStr(myArrayCount(i)), myArrayCriteria)
If bExistInArray = True Then
If iFound = 0 Then
'~~> First match
iFound = 1
Else
'~~> Other matches
iFound = iFound + 1
End If
End If
Next I
'~~> Returns number of Occurences
CountOccurencesBK = iFound
End Function
'***********************************************
'**** Check if lookup value exist in Array *****
'***********************************************
Function IsInArray(strLookup As String, Tabl) As Boolean
Dim Dimension As Byte, j As Integer

On Error Resume Next
If IsError(UBound(Tabl, 2)) Then Dimension = 1 Else Dimension = 2
On Error GoTo 0
Select Case Dimension
Case 1
On Error Resume Next
IsInArray = Application.Match(strLookup, Tabl, 0)
On Error GoTo 0
Case 2
For j = 1 To UBound(Tabl, 2)
On Error Resume Next
IsInArray = Application.Match(strLookup, Application.Index(Tabl, , j), 0)
On Error GoTo 0
If IsInArray = True Then Exit For
Next
End Select
End Function

Last edited:

#### Marcelo Branco

##### MrExcel MVP
A possible soultion using a UDF

Code:
Function CountOccur(s1 As String, s2 As String)
Dim spl As Variant, i As Long, counter As Long

spl = Split(s2, ",")
For i = LBound(spl) To UBound(spl)
counter = counter + (Len(s1) - Len(Replace(s1, spl(i), ""))) / Len(spl(i))
Next i
CountOccur = counter
End Function

 B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ L​ M​ 1​ Name​ Category​ Month​ String​ Result​ Should Be​ Criteria​ 2​ Tom​ Respite​ 01/11/2016​ Respond,Other,Reassessed​ 3​ 3​ Respond,Other,Reassessed​ 3​ Tom​ Other​ 01/11/2016​ Respond,Other,Reassessed​ 3​ 3​ 4​ Tom​ Reassessed​ 01/11/2016​ Respond,Other,Reassessed​ 3​ 3​ 5​ BK​ Respite​ 01/12/2016​ Respond​ 1​ 1​ 6​ Tom​ Other​ 01/12/2016​ Other,Other​ 2​ 2​ 7​ Tom​ Other​ 01/12/2016​ Other,Other​ 2​ 2​ 8​ Spider​ A​ 01/12/2016​ A​ 0​ 0​ 9​

Formula in G2 copied down (gray area)
=CountOccur(E2,\$M\$2)

Hope this helps

M.

#### Marcelo Branco

##### MrExcel MVP
A more robust code

Code:
Function CountOccur(s1 As String, s2 As String)
Dim spl As Variant, i As Long, counter As Long

spl = Split([B][COLOR=#0000ff]Replace(s2, " ", "")[/COLOR][/B], ",")
For i = LBound(spl) To UBound(spl)
counter = counter + (Len(s1) - Len(Replace(s1, spl(i), ""))) / Len(spl(i))
Next i
CountOccur = counter
End Function

M.

#### Marcelo Branco

##### MrExcel MVP
Inserting the criteria in different cells and using a formula...

 B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ L​ M​ 1​ Name​ Category​ Month​ String​ Formula​ Should Be​ Criteria​ 2​ Tom​ Respite​ 01/11/2016​ Respond,Other,Reassessed​ 3​ 3​ Respond​ 3​ Tom​ Other​ 01/11/2016​ Respond,Other,Reassessed​ 3​ 3​ Other​ 4​ Tom​ Reassessed​ 01/11/2016​ Respond,Other,Reassessed​ 3​ 3​ Reassessed​ 5​ BK​ Respite​ 01/12/2016​ Respond​ 1​ 1​ 6​ Tom​ Other​ 01/12/2016​ Other,Other​ 2​ 2​ 7​ Tom​ Other​ 01/12/2016​ Other,Other​ 2​ 2​ 8​ Spider​ A​ 01/12/2016​ A​ 0​ 0​ 9​

<tbody>
</tbody>

Formula in G2 copied down
=SUMPRODUCT((LEN(E2)-LEN(SUBSTITUTE(E2,\$M\$2:\$M\$4,"")))/LEN(\$M\$2:\$M\$4))

M.

Last edited:

#### Marcelo Branco

##### MrExcel MVP
oops..forgot to say:

About the formula in post #7
SUBSTITUTE is case sensitive.
If you don't want this, try
G2
=SUMPRODUCT((LEN(E2)-LEN(SUBSTITUTE(UPPER(E2),UPPER(\$M\$2:\$M\$4),"")))/LEN(\$M\$2:\$M\$4))

M.

Last edited:

#### Biz

##### Well-known Member
Thank you for the formula, it works well.
UDF in Post #7 - is generating the wrong results refer to Col N.

Excel 2013 32 bit
BCDEFGHIJKLMN
1NameCategoryMonthStringCalShould BeCriteria
2TomRespite1/11/2016Respond,Other,Reassessed33Respite,Other,Reassessed2
3TomOther1/11/2016Respond,Other,Reassessed332
4TomReassessed1/11/2016Respond,Other,Reassessed332
5BKRespite1/12/2016Respond110
6TomOther1/12/2016Other,Other222
7TomOther1/12/2016Other,Other222
8SpiderA1/12/2016A000

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
G2=CountOccurencesBK(E2,\$P\$2:\$P\$4)
G3=CountOccurencesBK(E3,\$P\$2:\$P\$4)
G4=CountOccurencesBK(E4,\$P\$2:\$P\$4)
G5=CountOccurencesBK(E5,\$P\$2:\$P\$4)
G6=CountOccurencesBK(E6,\$P\$2:\$P\$4)
G7=CountOccurencesBK(E7,\$P\$2:\$P\$4)
G8=CountOccurencesBK(E8,\$P\$2:\$P\$4)
N2=CountOccur(E2,\$M\$2)
N3=CountOccur(E3,\$M\$2)
N4=CountOccur(E4,\$M\$2)
N5=CountOccur(E5,\$M\$2)
N6=CountOccur(E6,\$M\$2)
N7=CountOccur(E7,\$M\$2)
N8=CountOccur(E8,\$M\$2)

<tbody>
</tbody>

<tbody>
</tbody>

Last edited:

#### Marcelo Branco

##### MrExcel MVP
Thank you for the formula, it works well.
UDF in Post #7 - is generating the wrong results refer to Col N.

Hey...you changed the criteria in M2
Respite,Other,Reassessed

Shouldn't it be?
Respond,Other,Reassessed

M.

Last edited:

Replies
3
Views
137
Replies
7
Views
244
Replies
0
Views
98
Replies
2
Views
167
Replies
1
Views
298

1,190,616
Messages
5,981,956
Members
439,745
Latest member
VBANewbieJohn

### 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.

### Which adblocker are you using?

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

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