# Counting blocks rather than amounts

#### JA-England

##### New Member
I am trying to determine a formula that will help me count the number of spells in a given range. For example if I need to count the spells of the letter 'a' in the following sequence.

a a b c a b c c b a a

Although the number of a's is 5 it is actually in 3 blocks. I gather the same formula will account for number.

Cheers

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I see no answers to yr query yet, so i have a few questions (for my benefit anyway) maybe for other as well.

1. I trust all these characters you used in yr example are in one cell.

2. So you are looking to count the number of frequencies (blocks)that "a" or "aa" or "aaa", etc appear in given string. Is that correct?

pll

=ROUNDUP((LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/2,0)

assumes your list is in A1.

Sorry, should have made it clear. They are all in separate cells.

Thanks for trying.

I seem to get a different count when i change the input to something a little different
Book1
ABCDEFG
6a a b c a b c c b a a
73ok
8
9
10a a b c a b c c b a a a a a a b a a b
116
12
13I think the answer should be 4 here
14
15
Sheet1

pll

Is this what you need?
Book1
BCDEFGHIJKLMN
17
18
19
20aabaaaabca
21
22
23so the answer you want is 3
24
25
Sheet1

pll

Thats the one?

Exactly.

You guys are on it over there.

Does it make any difference if I want it to work vertically, through a range A1:Z5?

This to loop down on a column basis, so if it starts at B5 and continues into C1 this counts as one spell.

Book3.xls
ABCDEFGHIJKLMNOPQRSTUVWXY
1aabcabccbaa3
2aabcabccbaaaaaabaab4
3
4
Sheet1

This also works... and, is a bit simplier...
Book3.xls
ABCDEFGHIJKLMNOPQRSTUVWXY
1aabcabccbaa3
2aabcabccbaaaaaabaab4
3
4
Sheet1

Replies
0
Views
115
Replies
1
Views
96
Replies
3
Views
111
Replies
1
Views
147
Replies
6
Views
277

1,203,506
Messages
6,055,800
Members
444,825
Latest member
aggerdanny

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