Count occurrences of text within a cell
 

I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0

Count occurrences of text within a cell
 

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E","")))

--
Biff
Microsoft Excel MVP


"Stan Sitek" wrote in message
...
I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0

Count occurrences of text within a cell
 

If you only use one cell then this will suffice

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E",""))

you probably meant


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"E","")))



--
Regards,

Peo Sjoblom



"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E","")))

--
Biff
Microsoft Excel MVP


"Stan Sitek" wrote in message
...
I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0

Count occurrences of text within a cell
 

Ooops!

Yeah, I meant your first example:

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E",""))


--
Biff
Microsoft Excel MVP


"Peo Sjoblom" wrote in message
...
If you only use one cell then this will suffice

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E",""))

you probably meant


=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),"E","")))



--
Regards,

Peo Sjoblom



"T. Valko" wrote in message
...
Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E","")))

--
Biff
Microsoft Excel MVP


"Stan Sitek" wrote in message
...
I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0

Count occurrences of text within a cell
 

Can you please tell me where a good tutorial on this subject is? Or what is
the significance of each part of this formula? I'm trying to expand my
knowledge of Excel. The formula works to count the number of 'V's' (in my
spreadsheet) in a range but I would like to return a value of 4 for each 'V'
counted.

Thanks.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E","")))

--
Biff
Microsoft Excel MVP


"Stan Sitek" wrote in message
...
I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0

Count occurrences of text within a cell
 

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"V","")))*4

For more on SUMPRODUCT see Bob Phillips' site.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Gord Dibben MS Excel MVP


On Wed, 20 Aug 2008 11:51:04 -0700, Deena
wrote:


Can you please tell me where a good tutorial on this subject is? Or what is
the significance of each part of this formula? I'm trying to expand my
knowledge of Excel. The formula works to count the number of 'V's' (in my
spreadsheet) in a range but I would like to return a value of 4 for each 'V'
counted.

Thanks.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"E","")))

--
Biff
Microsoft Excel MVP


"Stan Sitek" wrote in message
...
I need to count the number of characters repeating within a cell. For
example, assuming the following in range A1 through A4:

eee
ee
aaaae
af

A count of "e" would return

3
2
1
0