formula2

[ageeb]

Hi

My database consist of

A B C

Male 65 M65-M45-M22


I would like a formula to calculate the number of M's that occured that is to say that I have 3 M's in the column C, i.e to count M65 and then M45 and then M22 and to say I got 3

Thanks in advance

[sann0638]

A crude way would be to do Data...Text to Columns on column C, using M as a delimiter. You can then count the columns using a Count function.

[IML]

By formula you could use
=LEN(C1)-LEN(SUBSTITUTE(UPPER(C1),"M",""))

to see how many M's are in C1.

good luck.

[philR]

There is a REALLY simple way, depending on what format your data will be in. If you know that the only letter will be M, and the numbers will always be 2 characters, and they will always be separated by a single hyphen, hten you can just use the length of the string:

=(LEN(C2)+1)/4

...But I suspect what you want needs to be more versatile!

[ageeb]

On 2002-05-22 08:11, sann0638 wrote:
A crude way would be to do Data...Text to Columns on column C, using M as a delimiter. You can then count the columns using a Count function.

I am not sure if I understood what you meant

[philR]

Wow, IML just seen your reply. Very clever! Wish I had thought of that

[ageeb]

On 2002-05-22 08:24, philR wrote:
Wow, IML just seen your reply. Very clever! Wish I had thought of that

Hi

How can I reneralise the formula to work for more than one row, i.e

A B C
Male 65 M65-M45-M23
Male 45 M65-M21

How can I calculate the number of M's in the whole column of C, i.e the answer should be 5, Can u help me.

Thanks in advance

[Aladin Akyurek]

On 2002-05-23 02:25, ageeb wrote:

Hi

How can I reneralise the formula to work for more than one row, i.e

A B C
Male 65 M65-M45-M23
Male 45 M65-M21

How can I calculate the number of M's in the whole column of C, i.e the answer should be 5, Can u help me.

Thanks in advance

=SUMPRODUCT((LEN(C1:C20)-LEN(SUBSTITUTE(UPPER(C1:C20),"M",""))))

[ageeb]

On 2002-05-23 02:33, Aladin Akyurek wrote:

=SUMPRODUCT((LEN(C1:C20)-LEN(SUBSTITUTE(UPPER(C1:C20),"M",""))))

Thanks 4 your help, but I have another question if the database looks like this

A B C
Male 65 2M65

Is there a formula that can calculate that in column C I have 2M's instead of me writing it as M65-M65

Thanks in advance

[Aladin Akyurek]

On 2002-05-23 02:42, ageeb wrote:

Thanks 4 your help, but I have another question if the database looks like this

A B C
Male 65 2M65

Is there a formula that can calculate that in column C I have 2M's instead of me writing it as M65-M65

Thanks in advance

That would complicate the formula and make it very expensive qua time, in particular when you have entries like:

2M65-4M45

etc.