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.
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" <[email protected]> wrote in message
news:[email protected]...
>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.
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" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>
Perhaps also that 91 from A1:DB151 would appear to be column CM, and the Sumproduct is using CL
Originally Posted by Ken Wright
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" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
>
>
Try Tools, Formula Auditing, Evaluate Formula and click for each step of the evaluation, see which bit of your formula fails.
note also that your vlookup compared AA4 to Dump!A1 and you are now comparing to Dump!CQ
Last edited by Bryan Hessey; 12-04-2005 at 07:51 AM.
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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >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
> >
> >
which would have shown up as 'true' instead of '1' in the (now not-required) evaluation.
Good to see that you resolved it
[QUOTE=Nav]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.
Last edited by Bryan Hessey; 12-04-2005 at 08:05 AM.
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >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
> > >
> > >
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > >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
> > > >
> > > >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks