Dollar symbol not getting summed

Question

I want to manage my expenses using a Google spreadsheet. The problem is that I use dollars and euros. I write the numbers like $23 and €25. The sum function works properly for the euro symbol, but it returns zero for the $ sign.

I set up a small example here:

https://docs.google.com/spreadsheets/d/1_mg6xxsae2ybDHXQAqGLddXmJMDsYaaGkkhRY2YIIQc/edit#gid=0

Both columns has the same sum function, but only one works. Why?

---

After the creation of this question, I updated the example with some data from the real world. And I changed the timezone of the spreadsheet to the one that corresponds to me (Spain). I highlighted two examples in the sheet2. As you can see, the examples are using the correct currency format, but the sum stills zero. The same configuration in the sheet1 works nicely.

I'm starting to think the problem is the timezone of the spreadsheet.

Answer 1

I'm starting to think the problem is the timezone of the spreadsheet

no. the issue is that you are trying to sum plain text instead of numeric values. you can check this with:

=INDEX(ISNUMBER(D2:D64))

or with:

=INDEX(ISTEXT(D2:D64))

---

conversion from plain text to numeric values is done like:

=INDEX(IFERROR(1*IFERROR(REGEXREPLACE(D2:D; ".?(\d+).?(?:(\d+))?.?"; "$1,$2"); D2:D)))

---

to correctly sum your (sample) data of mixed origin use:

=INDEX(LET(c; TRIM(C2:C); d; D2:D; €; IFNA(REGEXEXTRACT(TO_TEXT(d); "[$€]")); 
 n; IFERROR(1*IFERROR(REGEXREPLACE(d; ".?(\d+).?(?:(\d+))?.?"; "$1,$2"); d)); 
 q; QUERY(HSTACK(c; €; n); 
 "select Col1,Col2,sum(Col3) where Col2 is not null group by Col1,Col2 label sum(Col3)''"); 
 TEXT(CHOOSECOLS(q; 1; 3); IF(INDEX(q;;2)="€"; 
 HSTACK("@"; "#,##0.00 €;-#,##0.00 €"); HSTACK("@"; "$#,##0.00_)_);-$#,##0.00_)_)")))))

---

or you can convert $ to € with GOOGLEFINANCE("USDEUR") rate like:

=INDEX(LET(c; TRIM(C2:C); d; D2:D; €; IFNA(REGEXEXTRACT(TO_TEXT(d); "[$€]")); 
 n; IFERROR(1*IFERROR(REGEXREPLACE(d; ".?(\d+).?(?:(\d+))?.?"; "$1,$2"); d)); 
 q; QUERY(HSTACK(c; €; n); 
 "select Col1,Col2,sum(Col3) where Col2 is not null group by Col1,Col2 label sum(Col3)''"); 
 TEXT(IF(INDEX(q;;2)="€"; CHOOSECOLS(q; 1; 3); HSTACK(INDEX(q;;1); 
 INDEX(q;;3)*GOOGLEFINANCE("USDEUR"))); HSTACK("@"; "_)#,##0.00 €;-#,##0.00 €"))))