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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
try
= len(concatinate(range))-len(substitute(concatinate(range),"A",""))
if A is the letter you want to count
 
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.
Click to expand...

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
 
Assuming the source letters are in col A,
maybe something like this in say, B1: =COUNTIF(A:A,"V")
 
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
 
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
 
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
 
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
 
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 -
Click to expand...

Well no, it gives me a #NOM? (name)
 
=SUMPRODUCT(--ISNUMBER(SEARCH("A",A1:A100)))





- Afficher le texte des messages précédents -
Click to expand...

Hello,

well...
on french syntax : =SUMPRODUCT(--ISNUMBER(SEARCH("A";A1:A100))) (note
the ;)
it gives me a #NOM? (NAME)
 
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
 
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
Click to expand...

Excellent one !

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