summing lookup results

[Martyn]

I have column in which each cell is a value returned from a LOOKUP. the
values are all numbers. A few of the cells simply have the number entered
manually.

I want to have a runnung total of the collumn at the bottom. But the SUM
formular only adds the numbers manually entered and does not add up the ones
returned by LOOKUP. How can I get it to add up all the values.

[Dav]

If the values are truly entered as numbers they will sum in the sum expression. They have been returned by the lookup as text, which might mean that they are text in the first place and hence are excluded from the calculation.

Your options are to turn them into values (the best option)

Or for expediency if you enter the floowing as an array presseing ctrl shift enter after entering it, it should be a workround

=SUM(E8:E12*1)

E8:E12 needs to be changed to the appropriate range of data

Regards

Dav

[Chris Marlow]

Hi,

It sounds like the 'numbers' being returned by the LOOKUP are actually text
(that just happen to be numbers).

If you wrap a VALUE function round your LOOKUP this should make the sum work.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"Martyn" wrote:

> I have column in which each cell is a value returned from a LOOKUP. the
> values are all numbers. A few of the cells simply have the number entered
> manually.
>
> I want to have a runnung total of the collumn at the bottom. But the SUM
> formular only adds the numbers manually entered and does not add up the ones
> returned by LOOKUP. How can I get it to add up all the values.

[Niek Otten]

Probably the numbers looked up are text, although they look like numbers.
Use

=VALUE(YourLookupFormula)

--
Kind regards,

Niek Otten

"Martyn" <[email protected]> wrote in message
news:[email protected]...
>I have column in which each cell is a value returned from a LOOKUP. the
> values are all numbers. A few of the cells simply have the number entered
> manually.
>
> I want to have a runnung total of the collumn at the bottom. But the SUM
> formular only adds the numbers manually entered and does not add up the
> ones
> returned by LOOKUP. How can I get it to add up all the values.

[Martyn]

Many thanks for all the quick replies.,

This method seemed the the quickest and worked perfectly.



"Dav" wrote:

>
> If the values are truly entered as numbers they will sum in the sum
> expression. They have been returned by the lookup as text, which might
> mean that they are text in the first place and hence are excluded from
> the calculation.
>
> Your options are to turn them into values (the best option)
>
> Or for expediency if you enter the floowing as an array presseing ctrl
> shift enter after entering it, it should be a workround
>
> =SUM(E8:E12*1)
>
> E8:E12 needs to be changed to the appropriate range of data
>
> Regards
>
> Dav
>
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=518133
>
>