Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possible
I can't get the count of the number of intances of a value to work.
ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possible
Hi Eric,
you could do using this module, press ALT+F11 copy it and paste. ___________________________________________ Function CountChar(MyChar, Mystring) Dim counter As Integer CountChar = 0 For counter = 1 To Len(Mystring) If Mid(Mystring, counter, 1) = MyChar Then CountChar = CountChar + 1 Next counter End Function __________________________________ So the formula will be =countchar("a",E7) does this help? regards from Brazil Marcelo "eric" escreveu: I can't get the count of the number of intances of a value to work. ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possible
=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a")
Will count the number of A's or a's in A1. If you want just the lower case a's: =(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a") eric wrote: I can't get the count of the number of intances of a value to work. ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possible
Thanks that worked.
Is there a way to get a total for a range of cells with the formula below? "Dave Peterson" wrote: =(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a") Will count the number of A's or a's in A1. If you want just the lower case a's: =(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a") eric wrote: I can't get the count of the number of intances of a value to work. ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possible
=(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a")
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "eric" wrote in message ... Thanks that worked. Is there a way to get a total for a range of cells with the formula below? "Dave Peterson" wrote: =(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a") Will count the number of A's or a's in A1. If you want just the lower case a's: =(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a") eric wrote: I can't get the count of the number of intances of a value to work. ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possible
Sorry, I meant
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER("a"),"")))/LEN(" a")) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "eric" wrote in message ... Thanks that worked. Is there a way to get a total for a range of cells with the formula below? "Dave Peterson" wrote: =(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a") Will count the number of A's or a's in A1. If you want just the lower case a's: =(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a") eric wrote: I can't get the count of the number of intances of a value to work. ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count # of instances in cell (a b a) answer 2 formula if possi
Great, that worked perfect. Thanks to everyone
"Bob Phillips" wrote: Sorry, I meant =SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),LOWER("a"),"")))/LEN(" a")) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "eric" wrote in message ... Thanks that worked. Is there a way to get a total for a range of cells with the formula below? "Dave Peterson" wrote: =(LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),lower("a"),"")))/LEN("a") Will count the number of A's or a's in A1. If you want just the lower case a's: =(LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/LEN("a") eric wrote: I can't get the count of the number of intances of a value to work. ex: In single cell (a b a) answer=2 for "a", but does not work with any formula I have tried. =countif(data, "*"&text&"*") brings an answer of 0. I was wondering if anyone knows how or if it is possbile to do this. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel: Allow combination of formula answer and text in one cell | Excel Worksheet Functions | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
Formula to count Cell colour. | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |