Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

=SUMPRODUCT(SUBTOTAL(4,OFFSET(G2:I4,ROW(G2:I4)-ROW(G2),0,1)),--(ISNUMBER(MATCH(F2:F4,A1:B1,0))))

Also, what would I need to change to
to give one less then max?

You're really pressing your luck!


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Thanks. But I really want to be able to use a formula that will calculate
the
total without having to enter "+" and the entire formula for each item I
add.
I will have many companies and items, and if I want a total of the whole
thing, I'll have to enter the whole formula many times adding everything
together.

Is there no way that I can total the whole thing using an array formula or
something else, and avoide having to enter the entire fomrula for each
item?

Thanks for any help.

"Teethless mama" wrote:

Try this:
=MAX((F2:F4=A1)*G2:I4)+MAX((F2:F4=B1)*G2:I4)

ctrl+shift+enter, not just enter



"art" wrote:

Many Thanks. However, can I use a formula that will calculate both
items in
one cell and give me the total. So if I enter in A1 Plastic and in A2
Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a
chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me
the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood
and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.