Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
I have a list of data in a different worksheet, and if I use vlookup ie.
=VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
You have AA4 in one formula and AA5 in another????
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
The formulae is the same for a whole column, the vlookup works for the whole
col, but the sum product does not work in any cell in the col. Hence I was testing the sumproduct formuale in the cell below where the vlookup was working. Any help is appreciated. Thanks "Ken Wright" wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL Ken Wright Wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
But this still has numbers in it so I would still expect a value to be shown.
Would this have to be formatted as number? Any further ideas anyone? "Bryan Hessey" wrote: Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL Ken Wright Wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
Try Tools, Formula Auditing, Evaluate Formula and click for each step of the evaluation, see which bit of your formula fails. Nav Wrote: But this still has numbers in it so I would still expect a value to be shown. Would this have to be formatted as number? Any further ideas anyone? "Bryan Hessey" wrote: Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL Ken Wright Wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
All - Thanks for your help, but I have just found the answer on the xldynamic
page. In case you were interested it needs a -- in front of it. ie. =SUMPRODUCT(--('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump'!CL3:CL101) ) Regards. "Nav" wrote: But this still has numbers in it so I would still expect a value to be shown. Would this have to be formatted as number? Any further ideas anyone? "Bryan Hessey" wrote: Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL Ken Wright Wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
which would have shown up as 'true' instead of '1' in the (now not-required) evaluation. Good to see that you resolved it Nav Wrote: All - Thanks for your help, but I have just found the answer on the xldynamic page. In case you were interested it needs a -- in front of it. ie. =SUMPRODUCT(--('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump'!CL3:CL101) ) Regards. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
As the author of that page, I can assure you that you are incorrect, it does
not need the double unary. You have double unaries or a * operator, you do not need both. What is wrong is that your original formula was missing brackets. You had =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) it should have been =SUMPRODUCT(('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump '!CL3:CL101)) or =SUMPRODUCT(--('Dump'!CQ3:CQ101=Holdings!AA5),'Dump'!CL3:CL101) as yours was doing an SP on the first condition, and as you had no operator to coerce the TRUE/FALSE to 1/0 it returned a total of 0, which was then multiplied by the other condition. 0 multiplied by anything is 0. -- HTH RP (remove nothere from the email address if mailing direct) "Nav" wrote in message ... All - Thanks for your help, but I have just found the answer on the xldynamic page. In case you were interested it needs a -- in front of it. ie. =SUMPRODUCT(--('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump'!CL3:CL101) ) Regards. "Nav" wrote: But this still has numbers in it so I would still expect a value to be shown. Would this have to be formatted as number? Any further ideas anyone? "Bryan Hessey" wrote: Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL Ken Wright Wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*------------ ---- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*------------ ---- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct and Vlookup
Bob, Thanks for that, it is good to learn and I like your page. This is the
first time I am using SumProduct, so it was an experience (I almost had it). "Bob Phillips" wrote: As the author of that page, I can assure you that you are incorrect, it does not need the double unary. You have double unaries or a * operator, you do not need both. What is wrong is that your original formula was missing brackets. You had =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) it should have been =SUMPRODUCT(('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump '!CL3:CL101)) or =SUMPRODUCT(--('Dump'!CQ3:CQ101=Holdings!AA5),'Dump'!CL3:CL101) as yours was doing an SP on the first condition, and as you had no operator to coerce the TRUE/FALSE to 1/0 it returned a total of 0, which was then multiplied by the other condition. 0 multiplied by anything is 0. -- HTH RP (remove nothere from the email address if mailing direct) "Nav" wrote in message ... All - Thanks for your help, but I have just found the answer on the xldynamic page. In case you were interested it needs a -- in front of it. ie. =SUMPRODUCT(--('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump'!CL3:CL101) ) Regards. "Nav" wrote: But this still has numbers in it so I would still expect a value to be shown. Would this have to be formatted as number? Any further ideas anyone? "Bryan Hessey" wrote: Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL Ken Wright Wrote: You have AA4 in one formula and AA5 in another???? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*------------ ---- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*------------ ---- "Nav" wrote in message ... I have a list of data in a different worksheet, and if I use vlookup ie. =VLOOKUP(AA4,'Dump'!$A$1:$DB$151,91,FALSE) - It brings back a value, however if I use Sumproduct =SUMPRODUCT('Dump'!CQ3:CQ101=Holdings!AA5)*('Dump' !CL3:CL101) It brings back 0, does anyone know why this is? The reason I need to use sumproduct is because some IDs have more that 1 row of data, so I need to sum it. Thanks in advance for any help/ideas. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=490533 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which SumProduct Sumif or VLookup? | Excel Discussion (Misc queries) | |||
VLOOKUP in SUMPRODUCT array | Excel Worksheet Functions | |||
I've tried Sumproduct, SumIf, Vlookup and Hlookup. | Excel Worksheet Functions | |||
Sumproduct with a vLookup, multiple criteria | Excel Worksheet Functions | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions |