|
#NUM!
Hello from Steved
{=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2))} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. |
SUMPRODUCT cannot handle entire column references. Change
your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2)) } I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . |
Hello from Steved
{=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1!D1:D2000 =$C$2))} Returns 0 Any ideas please. Thankyou. -----Original Message----- SUMPRODUCT cannot handle entire column references. Change your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2) )} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . . |
how about:
=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1!D1:D2000=$C$2)) or =SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000= $C$2)) (no need to array enter this) The -- stuff changes true/falses to 1/0's. And =sumproduct() likes to work with numbers. Steved wrote: Hello from Steved {=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1!D1:D2000 =$C$2))} Returns 0 Any ideas please. Thankyou. -----Original Message----- SUMPRODUCT cannot handle entire column references. Change your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2) )} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . . -- Dave Peterson |
|
Hello from Steved
=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) The above is giving me a 1 in this case it should be 4509. My understanding is sumproduct will copy a value and paste it. Am I using The right formula please. The above formula is in B2 of sheet2 it is to copy the value from Col A:A of Sheet1. Thankyou. -----Original Message----- how about: =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) or =SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000 =$C$2)) (no need to array enter this) The -- stuff changes true/falses to 1/0's. And =sumproduct() likes to work with numbers. Steved wrote: Hello from Steved {=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1! D1:D2000=$C$2))} Returns 0 Any ideas please. Thankyou. -----Original Message----- SUMPRODUCT cannot handle entire column references. Change your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2) )} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . . -- Dave Peterson . |
Hello from Steved
=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) The above is giving me a 1 in this case it should be 4509. My understanding is sumproduct will copy a value and paste it. Am I using The right formula please. The above formula is in B2 of sheet2 it is to copy the value from Col A:A of Sheet1. Thankyou. -----Original Message----- how about: =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) or =SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000 =$C$2)) (no need to array enter this) The -- stuff changes true/falses to 1/0's. And =sumproduct() likes to work with numbers. Steved wrote: Hello from Steved {=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1! D1:D2000=$C$2))} Returns 0 Any ideas please. Thankyou. -----Original Message----- SUMPRODUCT cannot handle entire column references. Change your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2) )} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . . -- Dave Peterson . |
The most that this can be is: 2000.
=SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1!D1:D2000=$C$2)) says to count the cells in C1:C2000 that match B2 and at the same time the cells in D1:D2000 that match C2. If all the cells match (correspondingly), then it can never exceed 2000. (Until you include more cells.) And formulas don't copy values and paste them. They do evaluate and put show you what the calculated value will be, though. Steved wrote: Hello from Steved =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) The above is giving me a 1 in this case it should be 4509. My understanding is sumproduct will copy a value and paste it. Am I using The right formula please. The above formula is in B2 of sheet2 it is to copy the value from Col A:A of Sheet1. Thankyou. -----Original Message----- how about: =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) or =SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1!D1:D2000 =$C$2)) (no need to array enter this) The -- stuff changes true/falses to 1/0's. And =sumproduct() likes to work with numbers. Steved wrote: Hello from Steved {=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1! D1:D2000=$C$2))} Returns 0 Any ideas please. Thankyou. -----Original Message----- SUMPRODUCT cannot handle entire column references. Change your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2) )} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . . -- Dave Peterson . -- Dave Peterson |
Thankyou.
-----Original Message----- The most that this can be is: 2000. =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) says to count the cells in C1:C2000 that match B2 and at the same time the cells in D1:D2000 that match C2. If all the cells match (correspondingly), then it can never exceed 2000. (Until you include more cells.) And formulas don't copy values and paste them. They do evaluate and put show you what the calculated value will be, though. Steved wrote: Hello from Steved =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) The above is giving me a 1 in this case it should be 4509. My understanding is sumproduct will copy a value and paste it. Am I using The right formula please. The above formula is in B2 of sheet2 it is to copy the value from Col A:A of Sheet1. Thankyou. -----Original Message----- how about: =SUMPRODUCT(--(Sheet1!C1:C2000=B2),--(Sheet1! D1:D2000=$C$2)) or =SUMPRODUCT((Sheet1!C1:C2000=B2)*(Sheet1! D1:D2000=$C$2)) (no need to array enter this) The -- stuff changes true/falses to 1/0's. And =sumproduct() likes to work with numbers. Steved wrote: Hello from Steved {=SUMPRODUCT((Sheet1!C1:C2000=B2),(Sheet1! D1:D2000=$C$2))} Returns 0 Any ideas please. Thankyou. -----Original Message----- SUMPRODUCT cannot handle entire column references. Change your references to something like: Sheet1!C1:C2000 HTH Jason Atlanta, GA -----Original Message----- Hello from Steved {=SUMPRODUCT((Sheet1!C:C=B2),(Sheet1!D:D=$C$2) )} I am getting a #NUM! The formula is in A2 in Sheet2. The object is to Find the value in Col A:A Sheet1 and copy it to Sheet2. Thankyou. . . -- Dave Peterson . -- Dave Peterson . |
| All times are GMT +1. The time now is 11:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com