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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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. :biggrin:

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

jl2509
  • Solved
Count iterations of a letter excluding other characters
2
Replies
16
Views
249
Peter_SSs
Peter_SSs
J
  • Solved
Calculations from multiple cells
Replies
7
Views
145
RobP
R
K
  • Question
Populate column C with repeated entries based on count for each row in column A?
Replies
3
Views
118
Peter_SSs
Peter_SSs
M
  • Solved
Count unique in column 1 within a date range in column b
Replies
3
Views
75
Fluff
Fluff
T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
373
Fluff
Fluff

Forum statistics

Threads
1,215,332
Messages
6,124,313
Members
449,152
Latest member
PressEscape

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