# QuestionFormula to SUM amount in any format

#### giovanni9720

FORMULA TO SUM AMOUNT IN ANY FORMAT
.
.
in the sheet "daily"
I would like to sum up all the amounts I enter in column amount 2, regardless of their size,
.
but as you can see for example in cell e7 of the sheet "daily", when in the column amount there is a number written so '1.000.00 , the formula gives me error, which I can not solve, can you help me please? thanks a lot
.
To be more specific, the error me since the amount pasted in the column "amount 2" has the point to separate the thousands
but while we are there I would like a formula that is fine even if I had to enter an amount of the type '1.000.00
.
can you do? I hope to be explained, thank you very much

#### kanewolf

##### Titan
Moderator
FORMULA TO SUM AMOUNT IN ANY FORMAT
.
.
in the sheet "daily"
I would like to sum up all the amounts I enter in column amount 2, regardless of their size,
.
but as you can see for example in cell e7 of the sheet "daily", when in the column amount there is a number written so '1.000.00 , the formula gives me error, which I can not solve, can you help me please? thanks a lot
.
To be more specific, the error me since the amount pasted in the column "amount 2" has the point to separate the thousands
but while we are there I would like a formula that is fine even if I had to enter an amount of the type '1.000.00
.
can you do? I hope to be explained, thank you very much
Since you have a leading quote, you are explicitly telling the software that value is a character string and not a number. Sum doesn't work with character strings.

#### giovanni9720

i paste the amount like text becouse if i don't do it, all the amount like 1000.00 would be seen like time and not like amount
becouse google sheet see the dot like a time and not like an amount

##### Polypheme
Moderator
Enter ALL numbers without periods, commas, quotes or any other extraneous characters.

#### kanewolf

##### Titan
Moderator
Enter ALL numbers without periods, commas, quotes or any other extraneous characters.
And allow the software to format the cells as you want.

#### giovanni9720

i solve the problem with this formula
=arrayformula( ifs( VAL.VUOTO(L5:L); SE.ERRORE(1/0); VAL.NUMERO(L5:L); VALORE(L5:L); VAL.NUMERO(SE.ERRORE(VALORE(SOSTITUISCI(regexreplace(L5:L; "\.(\d\d)\$"; ",\$1"); "."; "")))); VALORE(SOSTITUISCI(regexreplace(L5:L; "\.(\d\d)\$"; ",\$1"); "."; "")); VERO; L5:L ) )

l5: l contains amount as text, so it can be 1000.00 or 1.000,00 or anything else
i hope i will help someone else