Question Formula to SUM amount in any format

[giovanni9720]

FORMULA TO SUM AMOUNT IN ANY FORMAT
.
in the column "AMOUNT" OF THE FILE https:///docs.google.com/spreadsheets/d/1Sh86J7ChtibaRpNHugURH4ybBBZlXDDpR9np0IQfOd9w/edit?fbclid=IwAR0qTKQ30P6tJuq7ux7uxO11UcSt88qPhG7Wkic2Uc77fNcY1x7x2fcyJgNgid#=799112
.
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]

FORMULA TO SUM AMOUNT IN ANY FORMAT
.
in the column "AMOUNT" OF THE FILE https:///docs.google.com/spreadshee...cSt88qPhG7Wkic2Uc77fNcY1x7x2fcyJgNgid#=799112
.
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

 

[ex_bubblehead]

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

 

[kanewolf]

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