Question Formula to SUM amount in any format

Sep 16, 2022
4
0
10
0
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

Titan
Moderator
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
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.
 
Sep 16, 2022
4
0
10
0
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
 
Sep 16, 2022
4
0
10
0
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
 

ASK THE COMMUNITY

TRENDING THREADS