| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| trying to COUNT occurrences when certain criteria is met | Excel Discussion (Misc queries) | |||
| Count number of occurrences | Excel Discussion (Misc queries) | |||
| Count occurrences of values in a column??!! | Excel Worksheet Functions | |||
| Count unique occurrences of name | Excel Discussion (Misc queries) | |||
| how to count the nr of occurrences of a text string in a cell rang | Excel Worksheet Functions | |||
Linear Mode
