Re: letter number count

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <Alicia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4E99B395-0629-4898-91BE-4106E6CDF14B@xxxxxxxxxxxxxxxx
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" wrote:

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

Biff

"Alicia" <Alicia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E7EE249B-9C89-42C5-96A1-26F83BDBFB9C@xxxxxxxxxxxxxxxx
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" wrote:

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" <Alicia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9B3A5011-3665-4791-B61B-48548E82CA26@xxxxxxxxxxxxxxxx
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
work***,
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" wrote:

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

"Alicia" <Alicia@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5A05F622-806F-4C17-8834-B974936947E9@xxxxxxxxxxxxxxxx
I am looking for a way to count individual letters and numbers in
a
work***
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 work*** columns.

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

Alicia











.

Quantcast