Summing not summing

[doofy]

I've got a worksheet, and it references values in a second worksheet.
The values are transposed (via cell formula from the second worksheet)
onto the first worksheet.

When the cell formula is: ='sql link'!u3

The values from the second worksheet show up fine. So I have a column
of these:

='sql link'!u3
='sql link'!v3
='sql link'!w3
='sql link'!x3
='sql link'!y3

at the bottom I have a formula that sums those values:

=sum(d2:d6)

I get zero in that field, though the total of the values above do not
equal zero.

If I tried a test cell below the sum field:

=d2

and it correctly picks up the value of the field from the column above.
The sum field should work. Why isn't it?

 

[Bob Phillips]

Try this array formula

=SUM(--D26)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[doofy]

Try this array formula

=SUM(--D26)

comes back with #VALUE!

 

[Dave Thomas]

I did what you did and the sum is fine. I would need to see your workbook.

 

[doofy]

comes back with #VALUE!

This worked:

=d2+d3+d4+d5+d6


Odd.

 

[Bob Phillips]

Mine was an array formula, so enter with Ctrl-Shift-Enter, not just enter.

It is text, that is why SUM is failing.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Toppers]

If fields are text .....

=SUM(d2:d6) will give 0 but =d2+d3+d4+d5+d6 will give correct result!

 

[doofy]

If fields are text .....

=SUM(d2:d6) will give 0 but =d2+d3+d4+d5+d6 will give correct result!

Gotcha. I'll check that.

 

[doofy]

If fields are text .....

=SUM(d2:d6) will give 0 but =d2+d3+d4+d5+d6 will give correct result!

except its int's coming out of SQL Server, which is where I'm getting
the data from to fill the second worksheet.

 

[b&s]

Another approach...if fields are text .....

=SUMPRODUCT(--D26)

 

[doofy]

Another approach...if fields are text .....

=SUMPRODUCT(--D26)

how do I make the fields NOT text? They are coming from a sqlsvr table
and into the second worksheet as int's. Is it just as simple as
formatting the cells of the first worksheet as number rather than general?

 

[Dave Peterson]

Changing the number format doesn't change the value in the cell.

I like this technique.
select an empty cell
edit|copy
select the range to fix
edit|paste special|click Add

 

[Harlan Grove]

Try this array formula

=SUM(--D26)

....

Should have used

=SUMPRODUCT(--D26)

but if these values would be used in other calculations, probably better to
make each of them numbers, so

=--'sql link'!u3
=--'sql link'!v3
etc.

 

[Bob Phillips]

...

Should have used

=SUMPRODUCT(--D26)

No I shouldn't, array SUM works.

 

[Harlan Grove]

No I shouldn't, array SUM works.

True, but I meant in the sense of avoiding unnecessary follow-up and
responses when the OP failed to enter your SUM formula as an array formula.
Care to check the frequency of OPs failing to catch the 'array formula'
caveats in responses they receive?

'Course if you like the unnecessary addition back & forth . . .

 

[Bob Phillips]

True, but I meant in the sense of avoiding unnecessary follow-up and
responses when the OP failed to enter your SUM formula as an array
formula. Care to check the frequency of OPs failing to catch the 'array
formula' caveats in responses they receive?

'Course if you like the unnecessary addition back & forth . . .

Almost as much as your good self <bg>