SUMPRODUCT problem

  • Thread starter Thread starter AQAA
  • Start date Start date
A

AQAA

Hi,


i am trying to find the numbers 6,4,8 from sheet1 to popup on b1,b2,b3
and b4 on sheet3 everrytime when i enter
the names


Code:
--------------------


Sheet1

A B
=======================

tom 5
tom 6
tom 7
tom 9

john 4
john 5
john 7
john 9

Dave 5
Dave 6
Dave 7
Dave 8
Dave 9



Sheet2

A
===

6
4
8


Sheet3

A B
====================

Tom ?(6)
John ?(4)
Dave ?(8)



--------------------


I did this on B1 on sheet3


=SUMPRODUCT((sheet1!A1:A22=A1)*(Sheet1!B1:B22=Sheet2!A1:A39)*(Sheet2!A1:A39))

to get 6 but i got #N/A


Thanks in advance
 
It sounds like you should be trying to use a Vlookup
instead of a sumproduct...what are you summing?
 
Maybe you are write
I only need to find the numbers on sheet2 every time when I enter the name
on sheet3

Example:

Tom gets more product inclusive product Nr6 (sheet1)
and product nr6 is on sheet2

and I am know on sheet3 to find out if tom gets product nr6

Maybe I am wrong with SUMPRODUCT if there is another way
Please let me know

ThankX
 
Aqaa,

I'd like to help but don't fully understand your reasoning? If you type in
Tom, John of Dave you want to get 6, 4 and 8 to "pop up". What is the
reasoning behind the 6, 4 and 8? I see that on sheet 1 Tom has more
numbers than just 6 associated with his name. What is the criteria that
says it should be the 6 and not the 5?

Perhaps you could tell us a touch more about the problem.

Dan E
 
Ok,..
Sorry about my English ;-)

It is like this:-

4 = apple
5 = banana
6 = Limon
7 = orange
8 = pineapple
9 = wine press

On sheet1

I have the customer and the product they buy
on database list(access) like

A B C

tom 5 Banana
tom 6 Limon
tom 7 Orange
tom 9 wine press

So I converted the database file to Excel file
and I get the same list

and what I am trying to do is to get list like this
-----------------------------------------
Tom 5 6 7 9
-----------------------------------------

So I made another sheet (sheet2) with all the product

column a=banana b=Limon, etc...

On sheet3 I want to get

the name of the customer and the product he pays (article) on a one single
row

A1 = the name of customer B1,c1,d1,E1 and F1=the products(article)

so when I write tom on A1, I want to get B1=5,c1=6,d1=7,E1=Nothing F1=9

I hope I explained better this time


It is like a filter and the "popup" i mean to get / appear

ThankX again guys








Dan E said:
Aqaa,

I'd like to help but don't fully understand your reasoning? If you type in
Tom, John of Dave you want to get 6, 4 and 8 to "pop up". What is the
reasoning behind the 6, 4 and 8? I see that on sheet 1 Tom has more
numbers than just 6 associated with his name. What is the criteria that
says it should be the 6 and not the 5?

Perhaps you could tell us a touch more about the problem.

Dan E

Hi,


i am trying to find the numbers 6,4,8 from sheet1 to popup on b1,b2,b3
and b4 on sheet3 everrytime when i enter
the names


Code:
--------------------


Sheet1

A B
=======================

tom 5
tom 6
tom 7
tom 9

john 4
john 5
john 7
john 9

Dave 5
Dave 6
Dave 7
Dave 8
Dave 9



Sheet2

A
===

6
4
8


Sheet3

A B
====================

Tom ?(6)
John ?(4)
Dave ?(8)
Click to expand...
=SUMPRODUCT((sheet1!A1:A22=A1)*(Sheet1!B1:B22=Sheet2!A1:A39)*(Sheet2!A1:A39)
)
Click to expand...
 
Aqaa,

I'm still having a little trouble, send your sheet (if it's not too big) to
(e-mail address removed)
remove the nospam.'s

Dan E

AQAA said:
Ok,..
Sorry about my English ;-)

It is like this:-

4 = apple
5 = banana
6 = Limon
7 = orange
8 = pineapple
9 = wine press

On sheet1

I have the customer and the product they buy
on database list(access) like

A B C

tom 5 Banana
tom 6 Limon
tom 7 Orange
tom 9 wine press

So I converted the database file to Excel file
and I get the same list

and what I am trying to do is to get list like this
-----------------------------------------
Tom 5 6 7 9
-----------------------------------------

So I made another sheet (sheet2) with all the product

column a=banana b=Limon, etc...

On sheet3 I want to get

the name of the customer and the product he pays (article) on a one single
row

A1 = the name of customer B1,c1,d1,E1 and F1=the products(article)

so when I write tom on A1, I want to get B1=5,c1=6,d1=7,E1=Nothing F1=9

I hope I explained better this time


It is like a filter and the "popup" i mean to get / appear

ThankX again guys
Click to expand...
 
the sheet is on his way

ThankX for your help


Dan E said:
Aqaa,

I'm still having a little trouble, send your sheet (if it's not too big) to
(e-mail address removed)
remove the nospam.'s

Dan E
Click to expand...
 
Reply sent,

I couldn't figure out how your sheet2 was supposed to factor into
the equation

Dan E
 
My e-mail couldn't get through to your inbox!
Here is the formula for Sheet3 cell C3 (it can be dragged over and down)
Note the headers on sheet3 must have the same spelling as the
descriptions on sheet1... (ie orange and oranje won't work). However it
does find "banana" in "banana 7kg"

=SUMPRODUCT((Sheet1!$A$2:$A$10=$A3)*(ISERR(SEARCH(C$1,
Sheet1!$D$2:$D$10,1))=FALSE)*(Sheet1!$C$2:$C$10))

Dan E

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
The original message was received at Fri, 10 Oct 2003 15:19:27 -0600 (MDT)
from gdprpx03-port-9.dial.telus.net [161.184.232.148]

----- The following addresses had permanent fatal errors -----
<address_snipped>
(reason: 554 delivery error: dd Sorry, your message to address_snipped cannot be delivered. This account is over quota. -
mta103.mail.sc5.yahoo.com)

----- Transcript of session follows -----
.... while talking to mx1.mail.yahoo.com.:<<< 554 delivery error: dd Sorry, your message to address_snipped cannot be delivered. This account is over quota. -
mta103.mail.sc5.yahoo.com
554 5.0.0 Service unavailable
 
I don't need to find the article describe (sheet1 ColumnD)
i only want to find the article NR.(sheet1columnC)
SO not the TEXT but the numbersOnly

I really appreciate your help

ThankX


Dan E said:
My e-mail couldn't get through to your inbox!
Here is the formula for Sheet3 cell C3 (it can be dragged over and down)
Note the headers on sheet3 must have the same spelling as the
descriptions on sheet1... (ie orange and oranje won't work). However it
does find "banana" in "banana 7kg"

=SUMPRODUCT((Sheet1!$A$2:$A$10=$A3)*(ISERR(SEARCH(C$1,
Sheet1!$D$2:$D$10,1))=FALSE)*(Sheet1!$C$2:$C$10))

Dan E

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
The original message was received at Fri, 10 Oct 2003 15:19:27 -0600 (MDT)
from gdprpx03-port-9.dial.telus.net [161.184.232.148]

----- The following addresses had permanent fatal errors -----
<address_snipped>
(reason: 554 delivery error: dd Sorry, your message to address_snipped
Click to expand...
cannot be delivered. This account is over quota. -
mta103.mail.sc5.yahoo.com)

----- Transcript of session follows -----
... while talking to mx1.mail.yahoo.com.:
<<< 554 delivery error: dd Sorry, your message to address_snipped cannot
Click to expand...
be delivered. This account is over quota. -
 
Aqaa,

That's what the formula got, paste it into Sheet3 cell C3 and
drag over and down!

Dan E

AQAA said:
I don't need to find the article describe (sheet1 ColumnD)
i only want to find the article NR.(sheet1columnC)
SO not the TEXT but the numbersOnly

I really appreciate your help

ThankX


Dan E said:
My e-mail couldn't get through to your inbox!
Here is the formula for Sheet3 cell C3 (it can be dragged over and down)
Note the headers on sheet3 must have the same spelling as the
descriptions on sheet1... (ie orange and oranje won't work). However it
does find "banana" in "banana 7kg"

=SUMPRODUCT((Sheet1!$A$2:$A$10=$A3)*(ISERR(SEARCH(C$1,
Sheet1!$D$2:$D$10,1))=FALSE)*(Sheet1!$C$2:$C$10))

Dan E

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
The original message was received at Fri, 10 Oct 2003 15:19:27 -0600 (MDT)
from gdprpx03-port-9.dial.telus.net [161.184.232.148]

----- The following addresses had permanent fatal errors -----
<address_snipped>
(reason: 554 delivery error: dd Sorry, your message to address_snipped
Click to expand...
cannot be delivered. This account is over quota. -
mta103.mail.sc5.yahoo.com)

----- Transcript of session follows -----
... while talking to mx1.mail.yahoo.com.:
<<< 554 delivery error: dd Sorry, your message to address_snipped cannot
Click to expand...
be delivered. This account is over quota. -
mta103.mail.sc5.yahoo.com
554 5.0.0 Service unavailable
Click to expand...
Click to expand...
 
Back
Top