# Find and count

#### motilulla

##### Well-known Member
Hello,</SPAN></SPAN>

Here is an example for rows 3 that will be applied to all data are in C:K
</SPAN></SPAN>
For example if 2 consecutive cells has cell M1 values, then find 3rd if it is 5, 9 or M as per M1:O1 and count them (C3&D3=M | 5 so no match 5 | 5, Go To D3&E3= 5 | 5 Check F3=5 (so after 5 | 5 found 5 count 1 result in M2), Go To E3&F3= 5 | 5 Check G3=9 (so after 5 | 5 found 9 count 1 result in N2), DO COUNT ALL SAME WAY...
</SPAN></SPAN>

Note: for now I am using formula below but I want could this formula be shorter or any VBA solution (because in this example I have used 9 numbers, in fact I have 18 numbers so in the excel 2000 does not accept too long formula)
</SPAN></SPAN>

</SPAN></SPAN>
Using version 2000
</SPAN></SPAN>

Regards,
</SPAN>
Moti
</SPAN>

Last edited:

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### DSCfromCFA

##### Board Regular
G'day Moti,

I have found an answer for the 559 and the 55M conditions, because they can only occur in one way, however this answer does not handle 5555 being counted twice as (555)5 and 5(555)

The answer comes from here
http://www.cpearson.com/Excel/stringformulas.aspx

In the first instance write the full required string into N1 (559) & O1 (55M)

then concatenate C-K in cell L3
=CONCATENATE(C3,D3,E3,F3,G3,H3,I3,J3,K3)

then apply the following to the result of the 559 column and copy across to the 55M column

=IF(LEN(\$N\$1)=0,0,(LEN(\$L3)-LEN(SUBSTITUTE(UPPER(\$L3),UPPER(\$N\$1),"")))/LEN(\$N\$1))

Note: the Substitute function makes it non-case sensitive, i.e will work for both 55m and 55M and count them as the same thing

Last edited:

#### Akuini

##### Well-known Member
Try this:

Code:
``````[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1090492a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1090492-find-count.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va
[COLOR=Royalblue]Dim[/COLOR] d [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Object[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
va = Range([COLOR=brown]"C3:K"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=crimson]3[/COLOR])
z = Replace(Range([COLOR=brown]"M1"[/COLOR]), [COLOR=brown]" | "[/COLOR], [COLOR=brown]""[/COLOR])
k = [COLOR=crimson]2[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
d([COLOR=crimson]5[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=crimson]9[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=brown]"M"[/COLOR]) = [COLOR=crimson]0[/COLOR]
[COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR]) - [COLOR=crimson]2[/COLOR]
[COLOR=Royalblue]If[/COLOR] (va(i, j)) & (va(i, j + [COLOR=crimson]1[/COLOR])) = z [COLOR=Royalblue]Then[/COLOR] d(va(i, j + [COLOR=crimson]2[/COLOR])) = d(va(i, j + [COLOR=crimson]2[/COLOR])) + [COLOR=crimson]1[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

k = k + [COLOR=crimson]1[/COLOR]
Range([COLOR=brown]"M"[/COLOR] & k).Resize(, [COLOR=crimson]3[/COLOR]) = Application.Transpose(Application.Transpose(Array(d.items)))

[COLOR=Royalblue]Next[/COLOR]
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]``````

#### Marcelo Branco

##### MrExcel MVP
Maybe...

Formula in M3 copied across until O3 and down
=SUMPRODUCT(--(\$C3:\$I3=--LEFT(\$M\$1)),--(\$D3:\$J3=--RIGHT(\$M\$1)),--(\$E3:\$K3=M\$2))

Hope this helps

M.

#### Marcelo Branco

##### MrExcel MVP

A better solution - works if M1 contains, for example, M|5

M3 copied across and down
=SUMPRODUCT(--(\$C3:\$I3&""=LEFT(\$M\$1)),--(\$D3:\$J3&""=RIGHT(\$M\$1)),--(\$E3:\$K3=M\$2))

M.

#### motilulla

##### Well-known Member
G'day Moti,

I have found an answer for the 559 and the 55M conditions, because they can only occur in one way, however this answer does not handle 5555 being counted twice as (555)5 and 5(555)

The answer comes from here
http://www.cpearson.com/Excel/stringformulas.aspx

In the first instance write the full required string into N1 (559) & O1 (55M)

then concatenate C-K in cell L3
=CONCATENATE(C3,D3,E3,F3,G3,H3,I3,J3,K3)

then apply the following to the result of the 559 column and copy across to the 55M column

=IF(LEN(\$N\$1)=0,0,(LEN(\$L3)-LEN(SUBSTITUTE(UPPER(\$L3),UPPER(\$N\$1),"")))/LEN(\$N\$1))

Note: the Substitute function makes it non-case sensitive, i.e will work for both 55m and 55M and count them as the same thing

DSCfromCFA, Thank you for your attempt
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

#### motilulla

##### Well-known Member

Try this:

Code:
``````[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1090492a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1090492-find-count.html[/COLOR][/I]

[COLOR=royalblue]Dim[/COLOR] i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], k [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va
[COLOR=royalblue]Dim[/COLOR] d [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Object[/COLOR]
Application.ScreenUpdating = [COLOR=royalblue]False[/COLOR]
[COLOR=royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
va = Range([COLOR=brown]"C3:K"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=royalblue]End[/COLOR](xlUp).Row)
[COLOR=royalblue]ReDim[/COLOR] vb([COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] [COLOR=crimson]3[/COLOR])
z = Replace(Range([COLOR=brown]"M1"[/COLOR]), [COLOR=brown]" | "[/COLOR], [COLOR=brown]""[/COLOR])
k = [COLOR=crimson]2[/COLOR]
[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
d([COLOR=crimson]5[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=crimson]9[/COLOR]) = [COLOR=crimson]0[/COLOR]: d([COLOR=brown]"M"[/COLOR]) = [COLOR=crimson]0[/COLOR]
[COLOR=royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR]) - [COLOR=crimson]2[/COLOR]
[COLOR=royalblue]If[/COLOR] (va(i, j)) & (va(i, j + [COLOR=crimson]1[/COLOR])) = z [COLOR=royalblue]Then[/COLOR] d(va(i, j + [COLOR=crimson]2[/COLOR])) = d(va(i, j + [COLOR=crimson]2[/COLOR])) + [COLOR=crimson]1[/COLOR]
[COLOR=royalblue]Next[/COLOR]

k = k + [COLOR=crimson]1[/COLOR]
Range([COLOR=brown]"M"[/COLOR] & k).Resize(, [COLOR=crimson]3[/COLOR]) = Application.Transpose(Application.Transpose(Array(d.items)))

[COLOR=royalblue]Next[/COLOR]
Application.ScreenUpdating = [COLOR=royalblue]True[/COLOR]
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR]
[/FONT]``````
Akuini, that is amazing adapted for the range I required, I like the technique of your coding it is very undemanding and easy to modify amazing!</SPAN></SPAN>

I appreciate your help! Have a nice day
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

#### motilulla

##### Well-known Member
A better solution - works if M1 contains, for example, M|5

M3 copied across and down
=SUMPRODUCT(--(\$C3:\$I3&""=LEFT(\$M\$1)),--(\$D3:\$J3&""=RIGHT(\$M\$1)),--(\$E3:\$K3=M\$2))

M.
Marcelo Branco, I did not thought that the formula could be so tiny! I like the formula it works like a coding remarkable! </SPAN></SPAN>

I appreciate your help! Have a nice day
</SPAN></SPAN>

Kind Regards,
</SPAN></SPAN>
Moti
</SPAN></SPAN>

#### Akuini

##### Well-known Member
You're welcome, glad to help, & thanks for the feedback.

Replies
12
Views
183
Replies
4
Views
328
Replies
3
Views
195
Replies
5
Views
301
Replies
13
Views
683