0

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). 5 days 50 pushups

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 enter image description here

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

  • Use a column for each number. – Rubén Aug 24 '18 at 11:35
0

you can try entering numbers in B column like:

  • '5,5,2

enter image description here

or

  • ="5,5,2"

enter image description here

or

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

enter image description here

or

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

enter image description here

and then SUM it with a formula like:

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

enter image description here

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.