Samoan - hopefully you will quickly find out that there are many different
ways to solve this

one solution
Build a table like the following (in my example this information is in the
range b2:e9

Steve Red 1 2
Steve Red 2 4
Steve Red 3 6
Steve Red 4 8
Joe Red 1 3
Joe Red 2 5
Joe Red 3 7
Joe Red 4 9

g2:I2 have the inputs
Steve Red 3

then in J2 have the following formula

=SUMPRODUCT(--(B2:B9=G2),--(C2:C9=H2),--(D2:D9=I2),(E2:E9))

This will provide the answer of 6 (which is the multiplier that you were
looking for - if I read your message correctly). Does this help.

--
Wag more, bark less


"samoan" wrote:

I tried to be as detailed as possible:
I have version 2002 SP3

Can someone provide me a suggestion on formulas for the following:

Scenario - Steve and Joe have red, green, yellow and blue widgets. Each
widget weighs either 1,2,3 or 4 ounces. The price of the widget is based on
all three variables i.e a 1 ounce red widget owned by Steve has a different
cost then a 1 ounce red widget owned by Joe.

So, what I need are formulas that will populate cells with the correct
prices based on these variables. Ex: Steve owns six - 1 ounce red widgets.
Column A contains the name of the owner (Steve / Joe)
Column B contains the color of the widget.
Column C contains the weight of the widget (1,2,3,4 ozs).
Column D contains the number of widgets.

Based on how the formulas could work, I don't mind having separate columns
for the results for the different colors of widgets.
For example, if column E was the column to capture the cost for the red
widgets, the formula should give the answer to: If cloumn A = Steve and if
Column B = red, and if column C = 1, then the result in Column E should be
the amount in column D times $2 (cost of a 1 ounce red widget owned by
Steve), however, if cloumn A = Steve and if Column B = red, and if column C =
2, then the result in Column E should be the amount in column D times $4
(cost of a 2 ounce red widget owned by Steve), however, if cloumn A = Steve
and if Column B = red, and if column C = 3, then the result in Column E
should be the amount in column D times $6 (cost of a 3 ounce red widget owned
by Steve) however, if cloumn A = Steve and if Column B = red, and if column C
= 4, then the result in Column E should be the amount in column D times $8
(cost of a 4 ounce red widget owned by Steve) however If cloumn A = Joe and
if Column B = red, and if column C = 1, then the result in Column E should be
the amount in column D times $3 (cost of a 1 ounce red widget owned by Joe),
however, if cloumn A = Joe and if Column B = red, and if column C = 2, then
the result in Column E should be the amount in column D times $5 (cost of a 2
ounce red widget owned by Joe), however, if cloumn A = Joe and if Column B =
red, and if column C = 3, then the result in Column E should be the amount in
column D times $7 (cost of a 3 ounce red widget owned by Joe) however, if
cloumn A = Joe and if Column B = red, and if column C = 4, then the result in
Column E should be the amount in column D times $9 (cost of a 4 ounce red
widget owned by Joe)
Columns F, G and H could have the same formula but with a different color
for each column. I can sum the columns to get the value by color.

Tx,
Samoan