in office excel it just works straight away, i do the exact same thing, highlight the column that contains all the data i want to add and format cells into “currency” and then same command in a cell in same colmn
=sum(e1:e12)
press enter and it works so i just dont understand why it doesnt work in libre office calc
Please upload an example document in MS Excel format that you wrote about and together we will analyze possible differences in the resulting formula values in Excel and Calc.
This behavior is dictated by the standard:
6.16.61 SUM
Summary: Sum (add) the set of numbers, including all numbers in ranges.
If a cell contains text, its value will not be summed, regardless of whether that value can be converted to text.
ok i feel like a pratt now as i didnt notice this but every cell i wanted to sum up there was a single quote, ie '£223.75
so all i did was to remove all of them and now i can finally do =sum(a1:a12) and it works
sorry guys for all the mess and thank you guys for all your help
It may be that you have (incorrectly) imported these data from a CSV file. Make sure when importing that you select the correct locale for the currency type and ensure that Detect special Numbers is checked.
For fixing those pesky apostrphes after the fact see the FAQ.
