|
SUMPRODUCT
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 |
SUMPRODUCT
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 |
SUMPRODUCT
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 |
SUMPRODUCT
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 |
SUMPRODUCT
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 |
SUMPRODUCT
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 |
SUMPRODUCT
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 |
SUMPRODUCT
Maybe something like this would work then:
=SUMPRODUCT(--(TEXT(A1:A10)=TEXT(B1)),--(TEXT(S1:S10)=TEXT(T1))) I know it's a bandaid. "JMB" wrote: 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 |
SUMPRODUCT
I get "too few arguments" error. Perhaps:
=SUMPRODUCT(--(TEXT(A1:A10,"General")=TEXT(B1,"General")),--(TEXT(S1:S10,"General")=TEXT(T1,"General"))) "Barb Reinhardt" wrote: Maybe something like this would work then: =SUMPRODUCT(--(TEXT(A1:A10)=TEXT(B1)),--(TEXT(S1:S10)=TEXT(T1))) I know it's a bandaid. "JMB" wrote: 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 |
| All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com