frequency of an element or a number in a line

[seether]

I need to calculate the frequency of an element/number per line
The attached table explains it all!

[daddylonglegs]

What are the expected results?

[davesexcel]

I need to calculate the frequency of an element/number per line
The attached table explains it all!

Your picture asks for the number of "a"'s/the number of a's+b's
=COUNTIF(A1:J1,"*a*")/(COUNTIF(A1:J1,"*a*")+COUNTIF(A1:J1,"*b*"))

[seether]

Thanks Dave but it is giving me an error!
Look at the attached graphic

[seether]

What are the expected results?

the expected results are frequencies:
if a line contains:
aa ab bb aa
then there are 5 "a" and 3 "b" the result would be: 3/5+3

[NBVC]

Thanks Dave but it is giving me an error!
Look at the attached graphic

Are you perhaps on a European version of Excel...where ; is used as argument separators instead of ,

Please Login or Register  to view this content.

[seether]

Are you perhaps on a European version of Excel...where ; is used as argument separators instead of ,

Please Login or Register  to view this content.

Yep excellent! That solved it Thanks a lot NBVC :thumbs up:

[daddylonglegs]

That solved it

Are you sure?

COUNTIF won't count the number of "a"s and "b"s in a cell, it'll only count how many cells have "a" s or "b"s, e.g. for your above example

if a line contains:
aa ab bb aa
then there are 5 "a" and 3 "b" the result would be: 3/5+3

then you presumably want a result of 3/(3+5)=0.375?

COUNTIF formula will give =2/(3+2)=0.4

To get 3.75, assuming cells only contain "a"s and "b"s as per your example

=SUMPRODUCT(LEN(A1:J1)-LEN(SUBSTITUTE(A1:J1;"b";"")))/SUMPRODUCT(LEN(A1:J1))