SUMPRODUCT

[Mark Allen]

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

 

[Roger Govier]

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.

 

[Mark Allen]

Roger please see answers below:

Hi Mark

What do you have on Sheet Data in cell A3 and A19?

**A3 and A19 are on the calculation sheet S Wright. This is where I start
the equation from

Are they numbers or text? Are the numbers true numbers or Text numbers

** There is both numbers and text. ie. A3=Suzanne Wright and A19=CPS900

 

[Roger Govier]

Hi Mark

Try it as
=SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19))

Does that make any difference?

 

[Mark Allen]

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

 

[Mark Allen]

No still the same !!

Hi Mark

Try it as
=SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19))

Does that make any difference?

 

[Roger Govier]

Hi Mark

When you copied the data from the other workbook, did you Paste or Paste
Special>Values?
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.
Tools>Options>Calculation>Automatic

 

[Mark Allen]

Hi Roger, are you still there ??

Have you any more suggestions ??

Mark

 

[Roger Govier]

see response to your 16:29 post about column L

 

[Mark Allen]

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

 

[JMB]

What does the data look like - text, numbers, both? What is in A3 and A19?

Since you can edit a cell, press enter, and get it to work, it sounds like
the data is supposed to be numeric, but is coming in as text.

Try copying a blank cell, then select your range of data (that I assume is
supposed to be numeric) and click edit/paste special/Add. Does that do
anything?