0

The sum on my spreadsheet column Category always returns 0. enter image description here

I have formatted column Amount as Number but still it doesn't work. I had also removed all currency symbols from that column using find & replace. What do I have to change? It works flawlessly on MS Excel but not here.

Improve this question

3 Answers 3

Reset to default
1

You file Locale is UNITED KINGDOM and UK use dot . as decimal separator. So, google sheet treating comma , as string and your full column treating as string instead of number value. Either change Locale or change decimal separator. You can try the following formula by not changing anything in google-sheet.

=SUM(INDEX(--SUBSTITUTE(C4:C5,",",".")))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you harun, I simply changed the locale and it worked.
0

In your cells are stored as text rather than numbers, the formula will ignore them. This often happens when the numbers use a comma (,) as the decimal separator instead of a period (.).

Solution: You can select the cells in the range and change the format to "Number/Currency" using your spreadsheet software's formatting options.

Improve this answer

1 Comment

I formatted columns C and D as euro currency. The result is still the same. Please have a link at the spreadsheet.
0

Try this

The first answer's explanation is correct, though the solution provided is incorrect. You can use a formula to process the range and sum it.

Formula

=REDUCE(0,C2:C, LAMBDA(a,c, a+REGEXREPLACE(c,"[,]",".")))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.