Max function

[Ryan]

Hi All,

I need to calculate the Max value of A, B, and C in the following matrix.
Please help to advise which function to use:

Materials Days remain
A 7
A 10
B 4
C 5
A 5
B 8
A 2

Max (A) = ?
Max (B) = ?
Max (C) = ?

Thanks & regards,

 

[T. Valko]

Try this array formula** :

=MAX(IF(A1:A10="A",B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or, this normally entered version:

=SUMPRODUCT(MAX((A1:A10="A")*B1:B10))

 

[Teethless mama]

Try this:
None array enter

=MAX(INDEX((A1:A7="A")*B1:B7,))

or
=SUMPRODUCT(MAX((A1:A7="A")*B1:B7))

 

[Ryan]

Hi Valko,

Thanks a lot for your advice.
Because I have a list of 1,000 diffrent items of materials (from a,b,c,....
to item# 1000), I am looking the Max of the days remain in a database which
has about 8,000 lines.

I am afraid that the solution you suggested does not work in this case.

Please help with other solution?

Have a woderful day

Thanks & regards,
Long Ly

 

[T. Valko]

Hmmm...

I can't see why it wouldn't work.

With that many items you might want to look into creating a pivot table.

 

[Ryan]

Thanks a lot
--
Ryan L.

Try this:
None array enter

=MAX(INDEX((A1:A7="A")*B1:B7,))

or
=SUMPRODUCT(MAX((A1:A7="A")*B1:B7))

 

[Ryan]

Thanks a lot
--
Ryan L.

Try this array formula** :

=MAX(IF(A1:A10="A",B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or, this normally entered version:

=SUMPRODUCT(MAX((A1:A10="A")*B1:B10))