SUM(...) give 0 when the contents are INDIRECT(...) because they are not numeric

[Alameattemptatanonymity]

In cell F27 I have this formula: =INDIRECT(C27,0) Value: $550

In cell C27 I have this: =CONCATENATE(“R”,ROW(),“C”,3*ROW()-6) Value: R27C75

I have a whole column like that.

When I sum it, I get zero.

It should be the sum of the numbers that I am seeing as a result of the indirect references.

(It started when I tried to paste HTML from my bank’s website, and every row was shifted over 3 from the row before it)

(I have found that I could use OFFSET and it would simplify the situation considerably)

(Apparently this is more fun than doing my taxes!)

[mariosv]

Test if pasting as unformatted text using past special, so you can set up the options to get a better import.

[Alameattemptatanonymity]

thank you. i think i was afraid to paste it as unformatted text. i did not find the options yet and so far it pasted too much text into a single cell.

[mariosv]

[Ctrk+Shift+V] Menu/Edit/Paste special/unformatted text

[Alameattemptatanonymity]

The problem was that I did not have a numeric value in the cell being referred to.

So I hid all the uneven columns and added a column with the VALUE() of the INDIRECT result, and then when I sum it is is OK.

That is a good idea about the unformatted text.