Multiple count of letters in a column

S

Sparky

Board Regular
Joined
Feb 18, 2002
Messages
210
Office Version
  1. 2010
Platform
  1. Windows
I have a column (A:A) populated with single letters A, B, C etc. The column may have for example 30 consecutive A's and then 3 B's, 40 A's and then 2 B's etc. What I require in cell B1 is a count of the first lot of B's in column A, in cell C1 a count of the second lot of B's in column A and so on. Could anyone assist with this question please?

Thanks in advance
 
Sort by date Sort by votes
Hello Sparky,

Assuming you have up to 1000 letters in column A try this formula in B1

=IF(SUMPRODUCT(--($A1:$A1000="B"),--($A2:$A1001<>"B"))>=COLUMN()-COLUMN($A1),INDEX(FREQUENCY(IF($A1:$A1000="B",ROW($A1:$A1000)),IF($A1:$A1000<>"B",IF($A2:$A1001="B",ROW($A1:$A1000)))),COLUMN()-COLUMN($A1)+($A1<>"B")),"")

confirmed with CTRL+SHIFT+ENTER and copied across as far as necessary
 
Upvote 0
Or using VBA:

Code: 
Sub count()

Dim myCol As Long, myRange As Range, myCount As Long

myCount = 0
myCol = 2

For Each myRange In Range("A:A")

If myRange.Value = "B" Then

    myCount = myCount + 1

Else
    
    If myCount > 0 Then
    
        Cells(1, myCol).Value = myCount
        myCount = 0
        myCol = myCol + 1
        
    End If

End If

Next myRange
  
End Sub

Dom
 
Upvote 0
Assuming you have 34 entries in your list try this in B1 copied accross

LARGE(IF(FREQUENCY(COUNTIF(INDIRECT("A1:A"&ROW($1:$34)),"A"),COUNTIF(INDIRECT("A1:A"&ROW($1:$34)),"A"))>1,FREQUENCY(COUNTIF(INDIRECT("A1:A"&ROW($1:$34)),"A"),COUNTIF(INDIRECT("A1:A"&ROW($1:$34)),"A")),""),COLUMNS($B:B))-1

Confirmed with Ctrl + Shift + Enter

HTH
 
Upvote 0
Hi Sparky:

Let me see if I have understood you correctly ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABC
1A43
2A
3A
4A
5B
6B
7B
8B
9A
10A
11A
12B
13B
14B
15A
16A
17
Sheet2 (3)


</body></html>

My formulation counts the First set of B's (cell B1)and then counts the last set of B's (cell C1) in column A.
 
Upvote 0
Thanks for the response guys.

Barry Houdini your formula works fine. :-D

Domski your VBA code works to a degree, the reason being every time I update the file I have to run the code again. Or have I done something wrong? For the record I pasted it into a module.

Facethegod your formula did not work at all N/A errors.

Yogi Anand I could not understand your reponse (lines upon lines of HTML). Perhaps you could edit the post.

Once again thanks to all of you.
 
Upvote 0
Yogi

The formula would have to be copied across B1, C1, D1 etc in case there were further instances of the letter B occurring in column A, as in Barry Houdini's answer.

Thanks anyway
 
Upvote 0
Domski your VBA code works to a degree, the reason being every time I update the file I have to run the code again. Or have I done something wrong? For the record I pasted it into a module.
Click to expand...

No your not doing anything wrong. Unless they're based on worksheet or workbook etc events macro's need to be kicked of manually.

Just an example of another way to achieve the result you wanted really.

Dom
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Truncate and count names
Replies
6
Views
180
StephenCrump
S
jl2509
  • Solved
Count iterations of a letter excluding other characters
2
Replies
16
Views
572
Peter_SSs
Peter_SSs
J
  • Question Question
Pickup value from multiple criteria
Replies
0
Views
74
Jaffabfc
J
J
  • Question Question
Sorting a userform combobox list into an unusual order.
Replies
3
Views
67
jasonb75
J
U
  • Solved
VSTACK - can it combine specific columns of data
Replies
8
Views
381
usnapoleon
U

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top