Forum Discussion

Copper Contributor

Apr 26, 2022

Excel

If I enter a simple formula such as SUM(A1:A7), it gives me the wrong total and sometimes comes back as 0 when there are values in the listed cells. Doesn't seem to be a erroneous formula, so what c...

MVP

Apr 26, 2022

The most probable explanation is that A1:A7 contain text values (that may look like numbers).

You can try converting the values to 'real' numbers, or change the formula to

=SUM(--A1:A7)

Copper Contributor

Apr 26, 2022

Neither work. there are no text values and reformatting them didn't help. Your formula tweak with the two minus signs didn't work either

SergeiBaklan
Diamond Contributor
Apr 26, 2022
Cecil_Dunagan

How do you know is that text or not? Re-formatting doesn't convert texts to numbers and the opposite, at least you shall re-enter values after applying another format.

To check you may enter in any empty cell =ISTEXT(A1) or so.

IF =SUM(--A1:A7) doesn't work, perhaps =SUMPRODUCT(--A1:A7) or =SUMPRODUCT(A1:A7*1)
- Cecil_Dunagan
  Copper Contributor
  Apr 26, 2022
  Its a financial spreadsheet that has worked for several years. Never had text in it, just numbers'
HansVogelaar
MVP
Apr 26, 2022
Cecil_Dunagan

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.
- Cecil_Dunagan
  Copper Contributor
  Apr 26, 2022
  Hi Hans,
  
  Can't seem to be able to attach it here. Not sure if that is possible
  - HansVogelaar
    MVP
    Apr 26, 2022
    Cecil_Dunagan
    
    As I wrote: "if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar"

Resources