Roger,

the data comes from an SQL database straight into a new sheet..

how frustrating.....I am going to throw the lap top out of the window !!!!

Mark

"Roger Govier" wrote:

Hi Mark

When you copied the data from the other workbook, did you Paste or Paste
SpecialValues?
If there are formulae sat in column L and they haven't been evaluated,
then may this workbook has its calculation mode set to Manual.
ToolsOptionsCalculationAutomatic

--
Regards

Roger Govier


"Mark Allen" wrote in message
...
Roger,

this is something to do with the cell format for Data on L$1:L$10000
if I go
to one that is just a number edit and press return it then calculates
!!!

"Roger Govier" wrote:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?
Are they numbers or text? Are the numbers true numbers or Text
numbers
compared with the data in the relevant columns.
Does the Text have any leading or trailing spaces.

--
Regards

Roger Govier


"Mark Allen" <Mark wrote in message
...
I am trying to re-create a spreadsheet into a single file where
previously I
was linking to another but I am now having problems.

Original code:
=SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No
Prospects.xls'!$G$1:$G$10000=A19))

Returns a value of 2

New code:
=SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19))

Returns a value of 0 !!!!! should be 2

I know the cell range has changed i.e. D to C and G to L but this
should not
matter.

Please help as this is really frustrating !!!!

Mark