Excel formula count occurances

[Biz]

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

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

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

Excel 2013 32 bit

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula 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),",",", ") & ","))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[AliGW]

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]

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]

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>

Spreadsheet Formulas

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


　

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

 

[Marcelo Branco]

A possible soultion using a UDF

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]

A more robust 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]

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.

 

[Marcelo Branco]

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.

 

[Biz]

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

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

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula 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>

 

[Marcelo Branco]

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.