Count number of "1" in a cell

[pauldaddyadams]

Hi,

Can anyone help me with a formula please that will count the number of 1's in a cell.

e.g.
in a cell I may have:
1010110

I would need it to return the answer 4

I have uploaded an example file for reference

Paul

[Fotis1991]

Hi

In a cell(example H1), type the number that you want to count how many times is in Column D.

In E2 put the Array Formula(CSE)

=SUM(ISNUMBER(MATCH(TRANSPOSE(MID(D2;ROW(INDEX(D:D;1):INDEX(D:D;LEN(D2)));1));MID(H$1;ROW(INDEX(D:D; 1):INDEX(D:D;LEN(H$1)));1);0))+0)

Done!

Hope to helps you.

[pauldaddyadams]

Perfect - thank you!

Happy new year!

[Sadath31]

Hi pauldaddyadams

YOu have asked to count 1's within a cell
then use the formula
=SUMPRODUCT(--((MID(I2,ROW(INDIRECT("1:"&LEN(I2))),1))="1"))
sorry if I understood wrong.

[Fotis1991]

You are welcome

Happy new Year for you and for your own people!!!

[martindwilson]

since they are text just use
=LEN(D2)-LEN(SUBSTITUTE(D2,"1",""))