SUMPRODUCT Problem

  • Thread starter Thread starter Madiya
  • Start date Start date
M

Madiya

I have following data in a workbook.

Date Company Exchange Index Value Current
25-10-06 PNB BSE 11705.93 461.15
25-10-06 PNB NSE 3461.35 470.2
25-10-06 HIND BSE 11705.93 203.6
25-10-06 HIND NSE 3461.35 202.5
25-10-06 DS BSE 11705.93 41.1
25-10-06 DS NSE 3461.35 41.5
25-10-06 MRPL BSE 11705.93 50
25-10-06 MRPL NSE 3461.35 49.7
25-10-06 BOB BSE 11705.93 252
25-10-06 BOB NSE 3461.35 250.15
25-10-06 RCVOL BSE 11705.93 300
25-10-06 RCVOL NSE 3461.35 295.1
25-10-06 RIL BSE 11705.93 1027
25-10-06 RIL NSE 3461.35 1019
and so on .....................

These are stock data and keeps on adding rows daily.
In another workbook, I have list down the date, company and Exchange.
I want to put formula of sumproduct to match date, company,
exchange(already listed in the different cells) and return current. I
have created named ranges to the base workbook which I am using in the
following sumproduct formula but getting #VALUE! error.
=+SUMPRODUCT((Date=$A$1)*(Index="BSE")*(Company=A3)*(Price))

Can somebody tell me whats wrong in the formula please.

Regards,
Madiya
 
In the named ranges I have defined each range as
='[Copy of Market Today.xls]FE'!$A$1:$A$4996 with corrosponding
columns.
I thought there might be error in the range hance this additional post.

Regards,
Madiya
 
I think that you are using the wrong formula. Sumproduct takes two or
more ranges of numeric data multiplies the rows together and then
totals the products. A pivotTable may be more apprpriate for your
project.
A B
1 3 5
2 7 8

In this case A and B are columns and 1 and 2 are rows. So
=SUMPRODUCT(A1:A2,B1:B2)
would equal 71( 3*5 + 7*8).

In the named ranges I have defined each range as
='[Copy of Market Today.xls]FE'!$A$1:$A$4996 with corrosponding
columns.
I thought there might be error in the range hance this additional post.

Regards,
Madiya

I have following data in a workbook.

Date Company Exchange Index Value Current
25-10-06 PNB BSE 11705.93 461.15
25-10-06 PNB NSE 3461.35 470.2
25-10-06 HIND BSE 11705.93 203.6
25-10-06 HIND NSE 3461.35 202.5
25-10-06 DS BSE 11705.93 41.1
25-10-06 DS NSE 3461.35 41.5
25-10-06 MRPL BSE 11705.93 50
25-10-06 MRPL NSE 3461.35 49.7
25-10-06 BOB BSE 11705.93 252
25-10-06 BOB NSE 3461.35 250.15
25-10-06 RCVOL BSE 11705.93 300
25-10-06 RCVOL NSE 3461.35 295.1
25-10-06 RIL BSE 11705.93 1027
25-10-06 RIL NSE 3461.35 1019
and so on .....................

These are stock data and keeps on adding rows daily.
In another workbook, I have list down the date, company and Exchange.
I want to put formula of sumproduct to match date, company,
exchange(already listed in the different cells) and return current. I
have created named ranges to the base workbook which I am using in the
following sumproduct formula but getting #VALUE! error.
=+SUMPRODUCT((Date=$A$1)*(Index="BSE")*(Company=A3)*(Price))

Can somebody tell me whats wrong in the formula please.

Regards,
Madiya
Click to expand...
Click to expand...
 
That sounds as if you have a #VALUE in one of the ranges, or a text value in
the Price range. Check your data first.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Madiya said:
In the named ranges I have defined each range as
='[Copy of Market Today.xls]FE'!$A$1:$A$4996 with corrosponding
columns.
I thought there might be error in the range hance this additional post.

Regards,
Madiya

I have following data in a workbook.

Date Company Exchange Index Value Current
25-10-06 PNB BSE 11705.93 461.15
25-10-06 PNB NSE 3461.35 470.2
25-10-06 HIND BSE 11705.93 203.6
25-10-06 HIND NSE 3461.35 202.5
25-10-06 DS BSE 11705.93 41.1
25-10-06 DS NSE 3461.35 41.5
25-10-06 MRPL BSE 11705.93 50
25-10-06 MRPL NSE 3461.35 49.7
25-10-06 BOB BSE 11705.93 252
25-10-06 BOB NSE 3461.35 250.15
25-10-06 RCVOL BSE 11705.93 300
25-10-06 RCVOL NSE 3461.35 295.1
25-10-06 RIL BSE 11705.93 1027
25-10-06 RIL NSE 3461.35 1019
and so on .....................

These are stock data and keeps on adding rows daily.
In another workbook, I have list down the date, company and Exchange.
I want to put formula of sumproduct to match date, company,
exchange(already listed in the different cells) and return current. I
have created named ranges to the base workbook which I am using in the
following sumproduct formula but getting #VALUE! error.
=+SUMPRODUCT((Date=$A$1)*(Index="BSE")*(Company=A3)*(Price))

Can somebody tell me whats wrong in the formula please.

Regards,
Madiya
Click to expand...
Click to expand...
 
Thanks to both of you for reply.
Babbo :
I am trying to get the value itself and not sum or product.
I am using sumproduct in place of vlookup due to multiple criteria.

Bob:
I have checked the data sheet and there is no #value anywhere. All the
values are as expected.

Basically, I want to lookup date, company and exchange and want return
value corrosponding to above matching lookup values(all three should
match).

If there is any other formula, please let me know and I will try to use
that.

Regards,
Madiya.


Bob said:
That sounds as if you have a #VALUE in one of the ranges, or a text value in
the Price range. Check your data first.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Madiya said:
In the named ranges I have defined each range as
='[Copy of Market Today.xls]FE'!$A$1:$A$4996 with corrosponding
columns.
I thought there might be error in the range hance this additional post.

Regards,
Madiya

I have following data in a workbook.

Date Company Exchange Index Value Current
25-10-06 PNB BSE 11705.93 461.15
25-10-06 PNB NSE 3461.35 470.2
25-10-06 HIND BSE 11705.93 203.6
25-10-06 HIND NSE 3461.35 202.5
25-10-06 DS BSE 11705.93 41.1
25-10-06 DS NSE 3461.35 41.5
25-10-06 MRPL BSE 11705.93 50
25-10-06 MRPL NSE 3461.35 49.7
25-10-06 BOB BSE 11705.93 252
25-10-06 BOB NSE 3461.35 250.15
25-10-06 RCVOL BSE 11705.93 300
25-10-06 RCVOL NSE 3461.35 295.1
25-10-06 RIL BSE 11705.93 1027
25-10-06 RIL NSE 3461.35 1019
and so on .....................

These are stock data and keeps on adding rows daily.
In another workbook, I have list down the date, company and Exchange.
I want to put formula of sumproduct to match date, company,
exchange(already listed in the different cells) and return current. I
have created named ranges to the base workbook which I am using in the
following sumproduct formula but getting #VALUE! error.
=+SUMPRODUCT((Date=$A$1)*(Index="BSE")*(Company=A3)*(Price))

Can somebody tell me whats wrong in the formula please.

Regards,
Madiya
Click to expand...
Click to expand...
Click to expand...
 
SUMPRODUCT is the right formula, bobbo just isn't aware of this usage.

I am afraid I can't offer any other advice as I can't see the file. You need
to debug it, break the ranges down bit by bit until it doesn't fail, then
work back.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Madiya said:
Thanks to both of you for reply.
Babbo :
I am trying to get the value itself and not sum or product.
I am using sumproduct in place of vlookup due to multiple criteria.

Bob:
I have checked the data sheet and there is no #value anywhere. All the
values are as expected.

Basically, I want to lookup date, company and exchange and want return
value corrosponding to above matching lookup values(all three should
match).

If there is any other formula, please let me know and I will try to use
that.

Regards,
Madiya.


Bob said:
That sounds as if you have a #VALUE in one of the ranges, or a text value in
the Price range. Check your data first.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Madiya said:
In the named ranges I have defined each range as
='[Copy of Market Today.xls]FE'!$A$1:$A$4996 with corrosponding
columns.
I thought there might be error in the range hance this additional post.

Regards,
Madiya


Madiya wrote:

I have following data in a workbook.

Date Company Exchange Index Value Current
25-10-06 PNB BSE 11705.93 461.15
25-10-06 PNB NSE 3461.35 470.2
25-10-06 HIND BSE 11705.93 203.6
25-10-06 HIND NSE 3461.35 202.5
25-10-06 DS BSE 11705.93 41.1
25-10-06 DS NSE 3461.35 41.5
25-10-06 MRPL BSE 11705.93 50
25-10-06 MRPL NSE 3461.35 49.7
25-10-06 BOB BSE 11705.93 252
25-10-06 BOB NSE 3461.35 250.15
25-10-06 RCVOL BSE 11705.93 300
25-10-06 RCVOL NSE 3461.35 295.1
25-10-06 RIL BSE 11705.93 1027
25-10-06 RIL NSE 3461.35 1019
and so on .....................

These are stock data and keeps on adding rows daily.
In another workbook, I have list down the date, company and Exchange.
I want to put formula of sumproduct to match date, company,
exchange(already listed in the different cells) and return current. I
have created named ranges to the base workbook which I am using in the
following sumproduct formula but getting #VALUE! error.
=+SUMPRODUCT((Date=$A$1)*(Index="BSE")*(Company=A3)*(Price))

Can somebody tell me whats wrong in the formula please.

Regards,
Madiya
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

complicated sumproduct. 1
SUMPRODUCT - (amended) Exclude LAST Row of Matched Criteria (Month & Year) 2

Back
Top