letter number count

[Alicia]

I am looking for a way to count individual letters and numbers in a worksheet
have 2 columns 1 containing names and 1 containing numbers and would like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

 

[T. Valko]

Try this:

A1:A10 = names
B1:B10 = numbers

Enter this formula in D1:

=CHAR(ROW(A65))

Enter this formula in E1:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(UPPER(A$1:A$10),D1,"")))

Select both D1 and E1 and copy down to row 26

Enter this formula in G1:

=CHAR(ROW(A48))

Enter this formula in H1:

=SUMPRODUCT(LEN(B$1:B$10)-LEN(SUBSTITUTE(B$1:B$10,G1,"")))

Select both G1 and H1 and copy down to row 10

Biff

 

[Gord Dibben]

Can you deal with a macro?

Sub CountLetters()
''a count of each letter and number in used range outputted to a new sheet
Dim letCount(1 To 43) As Long
Dim Wksht As Worksheet
Dim ii As Long
Dim cell As Range
Dim WrkRng As Range
For Each Wksht In Worksheets
With Wksht
If .Name = "ListLetters" Then
Application.DisplayAlerts = False
Sheets("ListLetters").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set WrkRng = ActiveSheet.UsedRange

For Each cell In WrkRng
For ii = 1 To Len(cell)
If Mid(UCase(cell), ii, 1) Like "[0-9a-zA-Z]" Then
letCount(Asc(Mid(UCase(cell), ii, 1)) - 47) = _
letCount(Asc(Mid(UCase(cell), ii, 1)) - 47) + 1
End If
Next ii
Next cell
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "ListLetters"
CopytoSheet.Activate
Range("B1").Resize(43, 1).Value = Application.Transpose(letCount)
With Range("A1").Resize(43, 1)
.Formula = "=char(row()+47)"
.Value = .Value
End With
Range("A11:A17").EntireRow.Delete
End Sub


Gord Dibben MS Excel MVP

 

[Alicia]

Thank you, thank you, thank you !

Awesome, it worked, have been doing counts for quite a while and this will
certainly be a great help......Huge thank you !, will be a big time saver for
me.

I do have one more question, now that I have it set up on a worksheet,
formatted and prettied up with borders and such - how can I add column
headings without effecting the formulas ?

By the way, did I say thank you.... : )

Alicia

 

[T. Valko]

Just move the formula ranges down 1 row.

For example, if the letter formulas are in the range D1:E26, select that
range of cells and "grab" the border of the range with your mouse then drag
it down 1 row so that the new range is D2:E27. Then you can put headers in
row 1.

Biff

 

[Alicia]

Ok, got it its working fine, now it continues.....sorry

1) First question, how would I add a column for quantity per name and number
say A1 name and number would be 4 in the quantity and the next A2 would only
be 1 in the quantity didn't ask this initially and should have, obviously I
can sort by quantity but wanted to know if there was a way to have this
figured in since quantity amounts can vary quite a bit throughout my data ??

column A column B column C
name number quantity

SMITH 10 4
WILSON 25 1
ROBERTS 33 2


2) I understand the character reference now and have added counts for lower
case letters a,c,e & N with a tilde - char97, 99, 101 & 126 respectively. N
with the tilde is fine but it figures in the count for upper and lower case
letters a,c & e - have tried a couple of things but I am stuck....I thought
the upper and lower reference in the formula would take care of it but it
doesn't it gives the total count for whether its upper or lowercase alpha a,c
or e for both characters......


Hope you can help....

Alicia

 

[T. Valko]

Sorry, but I'm not following you on either question.

Biff

 

[Alicia]

Maybe this will help.....I hope

My intial problem was to have a count of occurences of A-Z and 0-9 in two
columns one containing names and one containing numbers which is working fine
but now I would like to add a column of quantity after each name and
number......

I might have 40 columns of name and number but for each name and number the
quantities vary....and wanted to know if there was a way to have this figured
into the formula since currently it only gives a count for one occurence for
each name and number listed....

also wanted to add lowercase letters ( just three lowercase letters a,e, & c
) and have a count given for those as well but it gives a total count of
upper and lower case for each i.e. if I have a total of 18 "A"'s and 4 "a"'s
in the names column it gives a result of 22 in both the A result and the a
result.

 

[T. Valko]

To count lowercase "a"

A1 = Aaron
A2 = Alan
A3 = Lisa

=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"a","")))
=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,CHAR(97),"")))

Result = 3

To count uppercase "A"

=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,"A","")))
=SUMPRODUCT(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,CHAR(65),"")))

Result = 2

The Substitute function is case sensitive.

I'm still not sure what kind of count you want?

Rich..........10
Sue...........20
Sue...........10
Tom..........15
Sue...........22

=COUNTIF(A1:A5,"Sue")

Result = 3

=COUNTIF(B1:B5,10)

Result = 2

Biff

 

[Alicia]

Thanks for the upper and lower case explanation, will check it out

As far as the count goes
First column is name, 2nd is number
and need to add a third column for quantity

Column A Column B "New C" Column
Name No. Qnty.

Smith 10 4
Joe 55 1
Mary 22 2

First calculation you gave me was for figuring occurences of A-Z and 0-9
in the name & number columns, now I need to add the quantity after the name
and number column C...don't know how else to explain it, sorry, quantities
vary
Basically need total occurences of A-Z and 0-9 including all 3 columns
in the case of Smith his name and number needs to be included in the count
times 4
joe his name and number needs to be included only once and in the case of Mary
her name and number needs to be included twice...... am I making sense ?
Need to have it set up somehow to do the calculation where the quantities
vary, it's working great but only if one occurence of each name and
number....have been doing a paste special into a spreadsheet to do the name
and number count now I would like to add a quantity column and paste special
that amount in and hopefully have it do the calculation.......

? Alicia

 

[pshepard]

A1:A10 - Name column in data
B1:B10 - Number column in data
C1:C10 - Count column in data
D126 - key in upper case alphabet - a through z
D2752 - key in lower case alphabet - a through z

E1:E26 - copy the following formula:
=SUMPRODUCT((LEN(A$1:A$10)*(C$1:C$10))-LEN(SUBSTITUTE(A$1:A$10,UPPER(D1),""))*(C$1:C$10))

E27:E52 - copy the following formula:
=SUMPRODUCT((LEN(A$1:A$10)*(C$1:C$10))-LEN(SUBSTITUTE(A$1:A$10,LOWER(D27),""))*(C$1:C$10))

G1:G10 - key in numbers, 0 through 9

H1:H10 - copy the following formula:
=SUMPRODUCT((LEN($B$1:$B$10)*($C$1:$C$10)-(LEN(SUBSTITUTE(B$1:B$10,G1,""))*($C$1:$C$10))))

This is the second time I have posted this - so please ignore this if you
see an earlier posting from me.

Thanks,

 

[Sandie Scrivens]

Hi Alicia

The COUNTIF function is the one you need.

B C D E

1 Tom
2 Dick =COUNTIF(C1:C5,"*o*")
3 Harry =COUNTIF(B1:B5,2)
2 George
5 Fred

The * in the first example is a wildcard thus counts cells with an o that
may have other characters on either side.

Hope this helps.