Count of numbers in Cells

[lprabhu1107]

Hi Experts,

Need solution for the below,

Weekdays expressed Monday as 1, Tuesday as 2 ....Sunday as 7

I have following in Column A

A1: 1 3 5
A2: 2 4 6
A3: 1 3 5 7
A4: 5 6 7
A5: 1 2 3 4 5 6

Numbers in Column A indicates weekdays. In corresponding column B i need Count of A1 as 3 in B1, 3 in B2, 4 in B3, 3 in B4, 6 in B5

Is this possible?

Regards

Prabhu

 

[mikerickson]

Perhaps =LEN(A1)-LEN(SUBSTITUTE(A1, " ", ""))+1

However if A1 contains 1 2 1 , the formula will return 3. Is this what you want even with the duplicated 1's?

 

[lprabhu1107]

Thank you!Formula is working amazingly!!!

Prabhu

 

[lprabhu1107]

Hi Mike,

I do not have situation of 1 2 1 in the entire column. hence no issues.

Thanks again

 

[West Man]

Alternatively; =ROUNDUP(LEN(A1)/2,0)

 

[Aladin Akyurek]

=LEN(SUBSTITUTE(A1, " ", "")

might also suffice.

 

[Rick Rothstein]

This simpler formula (one function call) will also work...

=(LEN(A1)+1)/2