EXCEL: how do I add multiple one digits in a singe cell? (tally)

[Guest]

Subject: Tallying e.g. 111=3
How do I make a single cell add three 1s?

Cell A1 has 11
Cell A2 has 111
Cell A3 has 1
Cell A4 has 1111

How do I get the sheet to create a total of 10?

 

[carlo]

if you are only concerned about the length of the cell try:
= len(a1) + len(a2) + len(a3) + len(a4)

hth

Carlo

 

[Pete_UK]

If you are sure there are only 1's in those cells, then you could use
LEN to count how many characters there are, eg:

=LEN(A1) will return 2
=LEN(A2) will return 3, etc.

So, you could put this formula in column B and copy down, and then sum
column B to get the overall total.

Hope this helps.

Pete

 

[Gord Dibben]

Enter this in B1 then drag/copy down.

=SUMPRODUCT(--MID($A1,ROW(INDIRECT("1:" & LEN($A1))),1))

Will SUM digits in the cells.

Then enter =SUM(B:B) in C1


Gord Dibben MS Excel MVP