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.

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.

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.

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

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

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

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

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

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

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