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

[Guest]

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.

 

[Guest]

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

 

[philmail]

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

 

[Guest]

Assuming the source letters are in col A,
maybe something like this in say, B1: =COUNTIF(A:A,"V")

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Bernie Deitrick]

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

 

[Max]

Mis-read posting, sorry.
Pl dismiss earlier response

---

 

[philmail]

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

 

[Guest]

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

 

[philmail]

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





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

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

 

[philmail]

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





- 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)

 

[Bernie Deitrick]

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]

Phillippe,

NB.Si is the same as COUNTIF, and can only be used to count either singlecells 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

Excellent one !

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