Ask Your Question
0

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

asked 2016-03-13 17:48:32 +0200

A lame attempt at anonymity gravatar image

updated 2016-03-13 20:19:30 +0200

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.

https://imgur.com/klb1rLz

(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!)

edit retag flag offensive close merge delete

Comments

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

m.a.riosv gravatar imagem.a.riosv ( 2016-03-13 18:21:38 +0200 )edit

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.

A lame attempt at anonymity gravatar imageA lame attempt at anonymity ( 2016-03-13 20:20:31 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2016-03-13 23:13:48 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-03-13 20:09:41 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-03-13 17:48:32 +0200

Seen: 57 times

Last updated: Mar 13 '16