Counting Asterisks

[Stephen_IV]

I am trying to count Asterisks in my data but to no avail.

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>DATA</th></tr></thead><tbody>
<tr><td>AAAA **</td></tr>
<tr><td>BBBB</td></tr>
<tr><td>CCCC ***</td></tr>
<tr><td>DDDD ****</td></tr>
<tr><td>EEEE</td></tr>
<tr><td>FFFF ***</td></tr>
<tr><td>GGGG ***</td></tr>
<tr><td>HHHH ***</td></tr>
<tr><td>IIII</td></tr>
<tr><td>JJJJ ***</td></tr>
<tr><td>KKKK ***</td></tr>
<tr><td>LLLL</td></tr>
<tr><td>MMMM</td></tr>
<tr><td>NNNN</td></tr>
<tr><td>OOOO ***</td></tr>
<tr><td>PPPP</td></tr>
<tr><td>QQQQ ***</td></tr>
<tr><td>RRRR ***</td></tr>
<tr><td>SSSS *</td></tr>
<tr><td>TTTT</td></tr>
<tr><td>UUUU</td></tr>
<tr><td>VVVV</td></tr>
<tr><td>WWWW ***</td></tr>
<tr><td>XXXX ***</td></tr>
<tr><td>YYYY ***</td></tr>
<tr><td>ZZZZ **</td></tr>
</tbody></table>

This is what I am looking for
<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Ast.</th><th>Count</th></tr></thead><tbody>
<tr><td>*</td><td>1</td></tr>
<tr><td>**</td><td>2</td></tr>
<tr><td>***</td><td>12</td></tr>
<tr><td>****</td><td>1</td></tr>
</tbody></table>

 

[PCL]

Assuming
data from B2 to B27
G3 = *
G4 = **
etc ...
then J3=NbAst($B$2:$B$27,LEN(G3)) , copy and paste as needed

with next UDF

Option Explicit


Function NbAst(WkRg As Range, Sel As Integer) As Integer
Dim Rg  As Range
Dim Nb  As Integer
    For Each Rg In WkRg
        Nb = Len(Rg) - Len(Replace(Rg, "*", ""))
        If (Nb = Sel) Then NbAst = NbAst + 1
    Next Rg
End Function

 

[Fluff]

How about

Book1
	A	B	C	D
1	DATA		Ast.	Count
2	AAAA **		****	1
3	BBBB		***	12
4	CCCC ***		**	2
5	DDDD ****		*	1
6	EEEE
7	FFFF ***
8	GGGG ***
9	HHHH ***
10	IIII
11	JJJJ ***
12	KKKK ***
13	LLLL
14	MMMM
15	NNNN
16	OOOO ***
17	PPPP
18	QQQQ ***
19	RRRR ***
20	SSSS *
21	TTTT
22	UUUU
23	VVVV
24	WWWW ***
25	XXXX ***
26	YYYY ***
27	ZZZZ **
Sheet1

Cell Formulas
Range	Formula
D2	=COUNTIF(A:A,"*"&SUBSTITUTE(C2,"*","~*"))-SUM(D$1:D1)

 

[Fluff]

Another option

Book1
	A	B	E	F
1	DATA		Ast.	Count
2	AAAA **		*	1
3	BBBB		**	2
4	CCCC ***		***	12
5	DDDD ****		****	1
6	EEEE
7	FFFF ***
8	GGGG ***
9	HHHH ***
10	IIII
11	JJJJ ***
12	KKKK ***
13	LLLL
14	MMMM
15	NNNN
16	OOOO ***
17	PPPP
18	QQQQ ***
19	RRRR ***
20	SSSS *
21	TTTT
22	UUUU
23	VVVV
24	WWWW ***
25	XXXX ***
26	YYYY ***
27	ZZZZ **
Sheet1

Cell Formulas
Range	Formula
F2	=SUMPRODUCT(--(RIGHT($A$2:$A$27,LEN(E2)+1)=" "&E2))

 

[PCL]

Assuming
data from B2 to B27
G3 = *
G4 = **
etc ...

Another one

J3=SUM(IF( LEN($B$2:$B$27)-LEN(SUBSTITUTE($B$2:$B$27,"*",""))=LEN(G3),1,0))
confirm using Control + Shift + enter

 

[PCL]

Assuming
data from B2 to B27
G3 = *
G4 = **
etc ...

Another one more simple ( No Control + Shift + enter )

J3=SUMPRODUCT(--(LEN($B$2:$B$27)-LEN(SUBSTITUTE($B$2:$B$27,"*",""))=LEN(G3)))

 

[Stephen_IV]

Thank you both very much!! I was scratching my head a little!!! But I appreciate all of your help and guidance!!!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[PCL]

Please to help
Enjoy Excel