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?

Try this array formula

=SUM(--D2:D6)

--
HTH

Bob

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

"doofy" wrote in message
...
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 wrote:
Try this array formula

=SUM(--D2:D6)


comes back with #VALUE!

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

"doofy" wrote in message
...
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?

doofy wrote:
Bob Phillips wrote:
Try this array formula

=SUM(--D2:D6)


comes back with #VALUE!


This worked:

=d2+d3+d4+d5+d6


Odd.

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)

"doofy" wrote in message
...
doofy wrote:
Bob Phillips wrote:
Try this array formula

=SUM(--D2:D6)


comes back with #VALUE!


This worked:

=d2+d3+d4+d5+d6


Odd.

If fields are text .....

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


"doofy" wrote:

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?

Toppers wrote:
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.

Toppers wrote:
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.

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

=SUMPRODUCT(--D2:D6)

Toppers wrote:
If fields are text .....

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


"doofy" wrote:

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?

--
pozdrav!
Berislav

b&s wrote:
Another approach...if fields are text .....

=SUMPRODUCT(--D2:D6)

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?

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



doofy wrote:

b&s wrote:
Another approach...if fields are text .....

=SUMPRODUCT(--D2:D6)

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

"Bob Phillips" wrote...
Try this array formula

=SUM(--D2:D6)
....

Should have used

=SUMPRODUCT(--D2:D6)

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.

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
Try this array formula

=SUM(--D2:D6)
...

Should have used

=SUMPRODUCT(--D2:D6)

No I shouldn't, array SUM works.

"Bob Phillips" wrote...
"Harlan Grove" wrote in message
"Bob Phillips" wrote...
Try this array formula

=SUM(--D2:D6)
...

Should have used

=SUMPRODUCT(--D2:D6)

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 . . .

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
"Harlan Grove" wrote in message
"Bob Phillips" wrote...
Try this array formula

=SUM(--D2:D6)
...

Should have used

=SUMPRODUCT(--D2:D6)

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 . . .


Almost as much as your good self <bg