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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

What are the values of A3 and A19? Are they both numeric?

"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

A3 is text and A19 is text, text and number or just number

"Barb Reinhardt" wrote:

What are the values of A3 and A19? Are they both numeric?

"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

Try something like this then

=SUMPRODUCT(--(A1:A10=B1),--(S2:S10 = T1))

HTH,

Barb Reinhardt

(Many of us review several Excel newsgroups. Multiposting is not necessary)

"Mark Allen" wrote:

A3 is text and A19 is text, text and number or just number

"Barb Reinhardt" wrote:

What are the values of A3 and A19? Are they both numeric?

"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

No, still the same.

What I don't get is why when I double click on the cell in the DATA sheet I
then press the enter key the contents of the cell move to the right (if a
number) and then the formula works.

However if it has got TEXT in the Cell it works fine...

Mark

"Barb Reinhardt" wrote:

Try something like this then

=SUMPRODUCT(--(A1:A10=B1),--(S2:S10 = T1))

HTH,

Barb Reinhardt

(Many of us review several Excel newsgroups. Multiposting is not necessary)

"Mark Allen" wrote:

A3 is text and A19 is text, text and number or just number

"Barb Reinhardt" wrote:

What are the values of A3 and A19? Are they both numeric?

"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

The numbers in your data are text. A text "1" is not equal to a 1 formatted
as a number. Maybe format A19 as text so that everything is a text
comparison.

Also see your other post for a means to coerce the numbers in your data to
actual numeric values.





"Mark Allen" wrote:

No, still the same.

What I don't get is why when I double click on the cell in the DATA sheet I
then press the enter key the contents of the cell move to the right (if a
number) and then the formula works.

However if it has got TEXT in the Cell it works fine...

Mark

"Barb Reinhardt" wrote:

Try something like this then

=SUMPRODUCT(--(A1:A10=B1),--(S2:S10 = T1))

HTH,

Barb Reinhardt

(Many of us review several Excel newsgroups. Multiposting is not necessary)

"Mark Allen" wrote:

A3 is text and A19 is text, text and number or just number

"Barb Reinhardt" wrote:

What are the values of A3 and A19? Are they both numeric?

"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark

The numbers in your data are most likely text. A text "1" is not equal to a
1 formatted as a number. Maybe format A19 as text so that everything is a
text comparison?

Also see your other post for a means to coerce the numbers in your data to
actual numeric values.




"Mark Allen" wrote:

No, still the same.

What I don't get is why when I double click on the cell in the DATA sheet I
then press the enter key the contents of the cell move to the right (if a
number) and then the formula works.

However if it has got TEXT in the Cell it works fine...

Mark

"Barb Reinhardt" wrote:

Try something like this then

=SUMPRODUCT(--(A1:A10=B1),--(S2:S10 = T1))

HTH,

Barb Reinhardt

(Many of us review several Excel newsgroups. Multiposting is not necessary)

"Mark Allen" wrote:

A3 is text and A19 is text, text and number or just number

"Barb Reinhardt" wrote:

What are the values of A3 and A19? Are they both numeric?

"Mark Allen" wrote:

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.

The data in "Data" sheet comes direct from a SQL database where previously
it came from a CSV file.

It has something to do with the format coming in from the database. If I go
to the data in column L double click on one item and then press enter the
data now moves to the left !

I don't understand ?

Please help as this is really frustrating !!!!

Mark