I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

try
= len(concatinate(range))-len(substitute(concatinate(range),"A",""))
if A is the letter you want to count

"spring022377" wrote:

I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

On 8 fév, 14:36, spring022377
wrote:
I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

Actually, I've got the same problem. I tried ton solve w NB.SI, it
works w 1 letter, but not with letter space letter, or letterletter...
Who could help ?

Philippe

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Assuming the source letters are in col A,
maybe something like this in say, B1: =COUNTIF(A:A,"V")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"spring022377" wrote:
I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Array enter - enter using Ctrl-Shift-Enter

=SUM(LEN($C$1:$C$100)-LEN(SUBSTITUTE($C$1:$C$100,A1,"")))

where A1 has the package code.

You can have a list starting in A1, and copy this formula down to match.

HTH,
Bernie
MS Excel MVP


"spring022377" wrote in message
...
I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Try:

=SUM(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"A","")))

Enter with Shift+Ctrl+Enter. Curly brackets {} will appear at either end of
string if entered correctly.

This will give number of occurrences of "A" in A1 to A10

HTH

"spring022377" wrote:

I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Philippe,

Same way, except you need to divide by the length of the string that you are looking for. Array
enter (enter using Ctrl-Shift-Enter)

=SUM(LEN($C$1:$C$100)-LEN(SUBSTITUTE($C$1:$C$100,A1,"")))/LEN(A1)


HTH,
Bernie
MS Excel MVP


Actually, I've got the same problem. I tried ton solve w NB.SI, it
works w 1 letter, but not with letter space letter, or letterletter...
Who could help ?

Philippe

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Mis-read posting, sorry.
Pl dismiss earlier response
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Maybe, I shall explain a bit better the problem.

Say the range A1:A10
Say each of these ten cells may contain one to six letters a, b, c, d,
e, f. I still don't know the syntax of it : abcdef or a b c d e f
Say I want to count in A11 the number of a (could be between 1 to 10)
Say I want to count in A12 the number of b (could be between 1 to 10)
etc...

If I use NB.Si, it's perfect as far as I've got just ONE letter per
cell.
Excel seems not to accept : =NB.SI(A1:A11;"a";"b",...).

So what ?

Philippe

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

=SUMPRODUCT(--ISNUMBER(SEARCH("A",A1:A100)))


"spring022377" wrote:

I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

On 8 fév, 14:55, Toppers wrote:
Try:

=SUM(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"A","")))

Enter with Shift+Ctrl+Enter. Curly brackets {} will appear at either end of
string if entered correctly.

This will give number of occurrences of "A" in A1 to A10

HTH



"spring022377" wrote:
I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Well no, it gives me a #NOM? (name)

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

On 8 fév, 15:13, Teethless mama
wrote:
=SUMPRODUCT(--ISNUMBER(SEARCH("A",A1:A100)))



"spring022377" wrote:
I am trying to figure out sales from an existing spreadsheet. The
spreadsheet containes packages (A-V) sold. Each cell may contain more than
one of each letter so I can't just use the CountA function because that only
counts the cell. I need something to actually count the letters to tell me
how many times it occurs in the range. Is this at all possible? It seems
like it would be and that it should be simple, but I am stuck. Please help!
Thanks.- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

Hello,

well...
on french syntax : =SUMPRODUCT(--ISNUMBER(SEARCH("A";A1:A100))) (note
the ;)
it gives me a #NOM? (NAME)

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

Phillippe,

NB.Si is the same as COUNTIF, and can only be used to count either single cells with the whole value
equal to the desired string, or count 1 for each string that has a letter. It cannot count doubles,
triples, etc within strings. If you need to count doubles, etc., then you need to use the array
formula:

=SUM(LEN($C$1:$C$100)-LEN(SUBSTITUTE($C$1:$C$100,"a","")))

Note:
LEN = NBCAR
SUM = SOMME
SUBSTITUTE = SUBSTITUE

Will count the number of a's in C1:C100, no matter where they are, how many spaces, or other
letters:

For example, with these five entries in C1:C5:

bac def
aa ad e f
a
b
ad a f a

the above formula will return 8.

If you want to find instances of "ad", then use

=SUM(LEN($C$1:$C$100)-LEN(SUBSTITUTE($C$1:$C$100,"ad","")))/LEN("ad")

and it will return 2.

If you DON'T need to count doubles, then

=COUNTIF(C1:C100,"*a*")

will return 4.

HTH,
Bernie
MS Excel MVP


"philmail" wrote in message
oups.com...
Maybe, I shall explain a bit better the problem.

Say the range A1:A10
Say each of these ten cells may contain one to six letters a, b, c, d,
e, f. I still don't know the syntax of it : abcdef or a b c d e f
Say I want to count in A11 the number of a (could be between 1 to 10)
Say I want to count in A12 the number of b (could be between 1 to 10)
etc...

If I use NB.Si, it's perfect as far as I've got just ONE letter per
cell.
Excel seems not to accept : =NB.SI(A1:A11;"a";"b",...).

So what ?

Philippe

I WANT TO COUNT HOW MANY TIMES A CERTAIN LETTER APPEARS IN A ROW
 

On 8 fév, 15:30, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Phillippe,

NB.Si is the same as COUNTIF, and can only be used to count either single cells with the whole value
equal to the desired string, or count 1 for each string that has a letter.. It cannot count doubles,
triples, etc within strings. If you need to count doubles, etc., then you need to use the array
formula:

=SUM(LEN($C$1:$C$100)-LEN(SUBSTITUTE($C$1:$C$100,"a","")))

Note:
LEN = NBCAR
SUM = SOMME
SUBSTITUTE = SUBSTITUE

Will count the number of a's in C1:C100, no matter where they are, how many spaces, or other
letters:

For example, with these five entries in C1:C5:

bac def
aa ad e f
a
b
ad a f a

the above formula will return 8.

If you want to find instances of "ad", then use

=SUM(LEN($C$1:$C$100)-LEN(SUBSTITUTE($C$1:$C$100,"ad","")))/LEN("ad")

and it will return 2.

If you DON'T need to count doubles, then

=COUNTIF(C1:C100,"*a*")

will return 4.

HTH,
Bernie
MS Excel MVP

"philmail" wrote in message

oups.com...

Maybe, I shall explain a bit better the problem.

Say the range A1:A10
Say each of these ten cells may contain one to six letters a, b, c, d,
e, f. I still don't know the syntax of it : abcdef or a b c d e f
Say I want to count in A11 the number of a (could be between 1 to 10)
Say I want to count in A12 the number of b (could be between 1 to 10)
etc...

If I use NB.Si, it's perfect as far as I've got just ONE letter per
cell.
Excel seems not to accept : =NB.SI(A1:A11;"a";"b",...).

So what ?

Philippe

Excellent one !

with the necessary french transpositions, it works...
Thanks a lot
Philippe