How to insert numerical values in a cell of a Google Sheet and how I can find their sum?

Question

I use Google Sheets mobile(Android).
I have one column with dates, and anoter column where in each cell I want to input several numerical values that corespond in each date, and in a third column the sum of those numerical values ( actually I plan to do 50 pushups every day for 5 days in several sets in each days, but I dont know the number of the daily sets).

The problem is that when I input numbers in column B seperated by either commas or spaces, the sheet converts the numbers into a date. For example when I insert in B2 5,5,2 it converts it in 5 5 2002

How can avoid this problem? (maybe with something like an arraylist? ) And if there is solution to the above problem, is there any way to input in the C column the sum of those numbers?

---

I don't know the number of columns, each time the number of sets are different

Answer 1

you can try entering numbers in B column like:

• '5,5,2

or

• ="5,5,2"

or

• =TEXT(; "5,5,2")

or

• =T("5,5,2")

and then SUM it with a formula like:

=SUM(SPLIT(B247;",";1;0))

Answer 2

The "normal" way to use a spreadsheet is to put each value on separate cells, so instead of

5,5,2

use

  A | B    |   C |   D |  E  | 
date| sets |     |     | sum |
 ...|     5|    5|    2| ... |

where A, B, C, D and E are built-in column headers.

Alternatives

1. Use another separator like |.
2. Prepend ' to prevent the automatic conversion to date.
3. Change the regional locale. I.E. Mexico doesn't convert 5,5,2 to date.
4. Add mininum 4 values something like 5,5,2,0.
5. To use a column for each value, if you don't know how many values will be, change the column order, date | sum | set and use =SUM(C2:2) to make the sum for each row. NOTE: If you will have many values use an array formula or MMULT instead.