I have multiple columns of data. I want Col T to insert a duty rate from a
refernce list depending on the country of supply in Col C and the type of
product in Col I. What formula do I need to enter so that it cross references
these 2 variables.

Thanks in anticipation.

Eqa

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty rate from a
refernce list depending on the country of supply in Col C and the type of
product in Col I. What formula do I need to enter so that it cross references
these 2 variables.

Thanks in anticipation.

Eqa

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a list of
all the countries and a list of all the product types? And how does it then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty rate from a
refernce list depending on the country of supply in Col C and the type of
product in Col I. What formula do I need to enter so that it cross references
these 2 variables.

Thanks in anticipation.

Eqa

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a list of
all the countries and a list of all the product types? And how does it then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty rate from a
refernce list depending on the country of supply in Col C and the type of
product in Col I. What formula do I need to enter so that it cross references
these 2 variables.

Thanks in anticipation.

Eqa

Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for the
same product from different countries.Forget that I have a look up list
because aI think this is cinfusing me. So tell me step by step what I need to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a list of
all the countries and a list of all the product types? And how does it then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty rate from a
refernce list depending on the country of supply in Col C and the type of
product in Col I. What formula do I need to enter so that it cross references
these 2 variables.

Thanks in anticipation.

Eqa

<<<"Forget that I have a look up list because aI think this is cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis* for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for the
same product from different countries.Forget that I have a look up list
because aI think this is cinfusing me. So tell me step by step what I need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a
list of
all the countries and a list of all the product types? And how does it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty rate
from a
refernce list depending on the country of supply in Col C and the
type of
product in Col I. What formula do I need to enter so that it cross
references
these 2 variables.

Thanks in anticipation.

Eqa

Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis* for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for the
same product from different countries.Forget that I have a look up list
because aI think this is cinfusing me. So tell me step by step what I need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a
list of
all the countries and a list of all the product types? And how does it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty rate
from a
refernce list depending on the country of supply in Col C and the
type of
product in Col I. What formula do I need to enter so that it cross
references
these 2 variables.

Thanks in anticipation.

Eqa

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for
the
same product from different countries.Forget that I have a look up list
because aI think this is cinfusing me. So tell me step by step what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a
list of
all the countries and a list of all the product types? And how does
it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty
rate
from a
refernce list depending on the country of supply in Col C and the
type of
product in Col I. What formula do I need to enter so that it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for
the
same product from different countries.Forget that I have a look up list
because aI think this is cinfusing me. So tell me step by step what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a
list of
all the countries and a list of all the product types? And how does
it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty
rate
from a
refernce list depending on the country of supply in Col C and the
type of
product in Col I. What formula do I need to enter so that it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

You can simply add additional ranges.

Just make sure that all the ranges are exactly the same size:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*($W$2: $W$50=C2)*$Z$2:$Z$50)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for
the
same product from different countries.Forget that I have a look up
list
because aI think this is cinfusing me. So tell me step by step what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or a
list of
all the countries and a list of all the product types? And how does
it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty
rate
from a
refernce list depending on the country of supply in Col C and
the
type of
product in Col I. What formula do I need to enter so that it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is
cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and
what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for
the
same product from different countries.Forget that I have a look up
list
because aI think this is cinfusing me. So tell me step by step what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or
a
list of
all the countries and a list of all the product types? And how
does
it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty
rate
from a
refernce list depending on the country of supply in Col C and
the
type of
product in Col I. What formula do I need to enter so that it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

The formula you recommended :
=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

It returns "0" instead of "456" which is suppose to be the right answer.

How many criteria can the formula allow??

Thanks!!

"RagDyer" wrote:

What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is
cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and
what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for
the
same product from different countries.Forget that I have a look up
list
because aI think this is cinfusing me. So tell me step by step what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or
a
list of
all the countries and a list of all the product types? And how
does
it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty
rate
from a
refernce list depending on the country of supply in Col C and
the
type of
product in Col I. What formula do I need to enter so that it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2: $F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.


"Tweetybird" wrote:

The formula you recommended :
=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

It returns "0" instead of "456" which is suppose to be the right answer.

How many criteria can the formula allow??

Thanks!!

"RagDyer" wrote:

What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is
cinfusing
me."

You *cannot* forget about this lookup list, because that is the *basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and
what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates for
the
same product from different countries.Forget that I have a look up
list
because aI think this is cinfusing me. So tell me step by step what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1 or
a
list of
all the countries and a list of all the product types? And how
does
it
then
distinguish which country and which product gets which duty rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a duty
rate
from a
refernce list depending on the country of supply in Col C and
the
type of
product in Col I. What formula do I need to enter so that it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

Your formula works fine for me. However, it will only work if each of the
lookup values and the corresponding data are of the same type (number or
text). For example, the 110 in column 2 and the 110 in column I must both be
numbers or both text. I suggest you use a formula such as =ISTEXT(I2)
(altering I2 to reference each cell in turn) to check this.

"Tweetybird" wrote in message
...
Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2: $F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.


"Tweetybird" wrote:

The formula you recommended :
=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

It returns "0" instead of "456" which is suppose to be the right answer.

How many criteria can the formula allow??

Thanks!!

"RagDyer" wrote:

What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit
!
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is
cinfusing
me."

You *cannot* forget about this lookup list, because that is the
*basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an
out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty
rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we
import
In Col C which is the duty payable dependent on which country
and
what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different
rates for
the
same product from different countries.Forget that I have a look
up
list
because aI think this is cinfusing me. So tell me step by step
what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and
I1 or
a
list of
all the countries and a list of all the product types? And
how
does
it
then
distinguish which country and which product gets which duty
rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a
duty
rate
from a
refernce list depending on the country of supply in Col C
and
the
type of
product in Col I. What formula do I need to enter so that
it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

If *you* copy your data from this thread, and paste it into a new sheet,
you'll see that the formula you're using works perfectly.

Since your using the asterisk form of Sumproduct(), that means the format of
Column G doesn't matter, as long as it looks like a number, it will
calculate.

That leaves Columns A and F ... and cells I2 and K2.

Column A and I2 must be the same!
Column F and K2 must be the same!

Manually key them in as a test, and I'll bet your formula will work.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Tweetybird" wrote in message
...
Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2: $F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.


"Tweetybird" wrote:

The formula you recommended :
=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

It returns "0" instead of "456" which is suppose to be the right answer.

How many criteria can the formula allow??

Thanks!!

"RagDyer" wrote:

What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is
cinfusing
me."

You *cannot* forget about this lookup list, because that is the
*basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an
out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty
rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and
what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates
for
the
same product from different countries.Forget that I have a look
up
list
because aI think this is cinfusing me. So tell me step by step
what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1
or
a
list of
all the countries and a list of all the product types? And how
does
it
then
distinguish which country and which product gets which duty
rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a
duty
rate
from a
refernce list depending on the country of supply in Col C
and
the
type of
product in Col I. What formula do I need to enter so that
it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa

I should have added:
Make sure the cells have the same format before keying in values.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"RagDyeR" wrote in message
...
If *you* copy your data from this thread, and paste it into a new sheet,
you'll see that the formula you're using works perfectly.

Since your using the asterisk form of Sumproduct(), that means the format of
Column G doesn't matter, as long as it looks like a number, it will
calculate.

That leaves Columns A and F ... and cells I2 and K2.

Column A and I2 must be the same!
Column F and K2 must be the same!

Manually key them in as a test, and I'll bet your formula will work.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Tweetybird" wrote in message
...
Hi,

I make myself clearer:
A B C D E F G
110 3 2A 1777 3 3000 456
111 4 5A 2587 2 3156 12.65
112 5 2A 3397 1 3312 -430.7
Put my criteria in column I, J and K:

Criteria 1 Criteria 2 Criteria 3 Value return from col H
110 2A 3000 0

The formula I use in column L "Value return from col H" is:
=SUMPRODUCT(($A$2:$A$11=I2)*($C$2:$C$11=J2)*($F$2: $F$11=K2)*$G$2:$G$11)

but the return value is "0".

Please enlighten. Thank you.


"Tweetybird" wrote:

The formula you recommended :
=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

It returns "0" instead of "456" which is suppose to be the right answer.

How many criteria can the formula allow??

Thanks!!

"RagDyer" wrote:

What formula did you use?
What exactly does "didn't work" mean?
No answer - wrong answer - error message ?

Remember ... all ranges must be the same size.

Did you use something like this:

=SUMPRODUCT(($W$2:$W$50=C2)*($X$2:$X$50=A2)*($Y$2: $Y$50=B2)*$Z$2:$Z$50)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Tweetybird" wrote in message
...
Hi RagDyeR,

Is the criteria only limited to 2? I tried 3 and it didn't work.

Thanks,
Tweety

"RagDyeR" wrote:

You're welcome, and thank you for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Great thanks for that RD.

Eqa

"RAGdyer" wrote:

<<<"Forget that I have a look up list because aI think this is
cinfusing
me."

You *cannot* forget about this lookup list, because that is the
*basis*
for
your required information..

Ideally, you could have a datalist of 3 columns, in an
out-of-the-way
location, say X, Y, and Z,
where X contains the countries, Y the product type, and Z the duty
rate
for
the corresponding row combination of country and product.

Say your datalist went from X2 to Z50.
Say your dropdowns started in A2 and B2.

Enter this in C2:

=SUMPRODUCT(($X$2:$X$50=A2)*($Y$2:$Y$50=B2)*$Z$2:$ Z$50)

And copy down as needed.
--
HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit
!
----------------------------------------------------------------------------------------

"Eqa" wrote in message
...
Eric,

I don't think I explained myself clearly.

I'll start again:
Col A has a drop down box with all the countries we import fom,
Col B has a drop down box with all the types of product we import
In Col C which is the duty payable dependent on which country and
what
type
of product it is a certain duty rate applies.

What do I need to do so that this will understand different rates
for
the
same product from different countries.Forget that I have a look
up
list
because aI think this is cinfusing me. So tell me step by step
what I
need
to
establish so that this works.

Hope this is clear and thanks,

Eqa

"EricBB" wrote:

say your duty rate is in col D,
=sumproduct(--(c1:c10="what country?"),--(i1:i10="product
type"),--(d1:d10))
change the range to suit your needs.

"Eqa" wrote:

Eric,

What do I place in logical 1 and logical 2? the cell C1 and I1
or
a
list of
all the countries and a list of all the product types? And how
does
it
then
distinguish which country and which product gets which duty
rate?

Eqa

"EricBB" wrote:

try this,
=sumproduct(--(logical 1),--(logical 2), col of duty rate)

"Eqa" wrote:

I have multiple columns of data. I want Col T to insert a
duty
rate
from a
refernce list depending on the country of supply in Col C
and
the
type of
product in Col I. What formula do I need to enter so that
it
cross
references
these 2 variables.

Thanks in anticipation.

Eqa