Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky

ok tks.
on sheet 1, I have all the information
column A: fruit names
column B: quantity
colunm C: origin
column D: category

on sheet 2, I have a resume where I will put:
cell B2: I enter the fruit name
cell B3: I enter the origin
cell B4: I enter the category

In cell D3 , I would like to have the value according to criteria in cell
B2+B3+B4.
Is it possible?
--
lucky


"akphidelt" wrote:

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky

Alright, im a little lost. Whats this value that you are talking about? Is it
the quantity?

"luckyie" wrote:

ok tks.
on sheet 1, I have all the information
column A: fruit names
column B: quantity
colunm C: origin
column D: category

on sheet 2, I have a resume where I will put:
cell B2: I enter the fruit name
cell B3: I enter the origin
cell B4: I enter the category

In cell D3 , I would like to have the value according to criteria in cell
B2+B3+B4.
Is it possible?
--
lucky


"akphidelt" wrote:

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky

sorry, yes I wan the quantity to show according to the 3 criteria I would
have entered.
tks
--
lucky


"akphidelt" wrote:

Alright, im a little lost. Whats this value that you are talking about? Is it
the quantity?

"luckyie" wrote:

ok tks.
on sheet 1, I have all the information
column A: fruit names
column B: quantity
colunm C: origin
column D: category

on sheet 2, I have a resume where I will put:
cell B2: I enter the fruit name
cell B3: I enter the origin
cell B4: I enter the category

In cell D3 , I would like to have the value according to criteria in cell
B2+B3+B4.
Is it possible?
--
lucky


"akphidelt" wrote:

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky

Put this in D3 of Sheet2:

=SUMPRODUCT((Sheet1!A1:A100=B2)*(Sheet1!C1:C100=B3 )*(Sheet1!
D1:D100=B4)*(Sheet1!B1:B100))

I've assumed you have up to 100 rows on Sheet1 - adjust this if you
have more.

Hope this helps.

Pete

On Aug 21, 10:56*pm, luckyie
wrote:
sorry, yes I wan the quantity to show according to the 3 criteria I would
have entered.
tks
--
lucky



"akphidelt" wrote:
Alright, im a little lost. Whats this value that you are talking about? Is it
the quantity?

"luckyie" wrote:

ok tks.
on sheet 1, I have all the information
column A: fruit names
column B: quantity
colunm C: origin
column D: category

on sheet 2, I have a resume where I will put:
cell B2: I enter the fruit name
cell B3: I enter the origin
cell B4: I enter the category

In cell D3 , I would like to have the value according to criteria in cell
B2+B3+B4.
Is it possible?
--
lucky

"akphidelt" wrote:

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky- Hide quoted text -

- Show quoted text -

TKS

but I don't want to add or multiply values. I would like to have a value
return based on 3 paramaters/criteria.
--
lucky


"Pete_UK" wrote:

Put this in D3 of Sheet2:

=SUMPRODUCT((Sheet1!A1:A100=B2)*(Sheet1!C1:C100=B3 )*(Sheet1!
D1:D100=B4)*(Sheet1!B1:B100))

I've assumed you have up to 100 rows on Sheet1 - adjust this if you
have more.

Hope this helps.

Pete

On Aug 21, 10:56 pm, luckyie
wrote:
sorry, yes I wan the quantity to show according to the 3 criteria I would
have entered.
tks
--
lucky



"akphidelt" wrote:
Alright, im a little lost. Whats this value that you are talking about? Is it
the quantity?

"luckyie" wrote:

ok tks.
on sheet 1, I have all the information
column A: fruit names
column B: quantity
colunm C: origin
column D: category

on sheet 2, I have a resume where I will put:
cell B2: I enter the fruit name
cell B3: I enter the origin
cell B4: I enter the category

In cell D3 , I would like to have the value according to criteria in cell
B2+B3+B4.
Is it possible?
--
lucky

"akphidelt" wrote:

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky- Hide quoted text -

- Show quoted text -

Why don't you try Pete's formula? Just for fun.

The only value you are adding is the quantity. The other 3 aspects of the
formula will equal 1 if a condition is met, or 0 if a condition is not met,
so in order for the quantity to add in, the formula would be calculating as:
1*1*1*quantity
If any of the conditions are not met, then the 1 turns to a zero, and thus
not adding the quantity.
Like Spiky says, give the formula a try.

--
John C


"luckyie" wrote:

TKS

but I don't want to add or multiply values. I would like to have a value
return based on 3 paramaters/criteria.
--
lucky


"Pete_UK" wrote:

Put this in D3 of Sheet2:

=SUMPRODUCT((Sheet1!A1:A100=B2)*(Sheet1!C1:C100=B3 )*(Sheet1!
D1:D100=B4)*(Sheet1!B1:B100))

I've assumed you have up to 100 rows on Sheet1 - adjust this if you
have more.

Hope this helps.

Pete

On Aug 21, 10:56 pm, luckyie
wrote:
sorry, yes I wan the quantity to show according to the 3 criteria I would
have entered.
tks
--
lucky



"akphidelt" wrote:
Alright, im a little lost. Whats this value that you are talking about? Is it
the quantity?

"luckyie" wrote:

ok tks.
on sheet 1, I have all the information
column A: fruit names
column B: quantity
colunm C: origin
column D: category

on sheet 2, I have a resume where I will put:
cell B2: I enter the fruit name
cell B3: I enter the origin
cell B4: I enter the category

In cell D3 , I would like to have the value according to criteria in cell
B2+B3+B4.
Is it possible?
--
lucky

"akphidelt" wrote:

SUMPRODUCT

Or if you gave a little example it would be easier to explain

"luckyie" wrote:

Hi everyone,

I am trying to creata a formula that will return a value depending on 3
criteria. With Vlookup I could only find the formula retrieving a value based
on one criteria. Any idea on how to return a value based on 3 criteria. tks
--
lucky- Hide quoted text -

- Show quoted text -