summing lookup results

[Guest]

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

 

[Guest]

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.

 

[Niek Otten]

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

=VALUE(YourLookupFormula)

 

[Guest]

Many thanks for all the quick replies.,

This method seemed the the quickest and worked perfectly.