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,
--
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))


--
Biff
Microsoft Excel MVP


"Ryan" wrote in message
...
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,
--
Ryan L.

Try this:
None array enter

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

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



"Ryan" wrote:

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,
--
Ryan L.

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


--
Ryan L.


"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"Ryan" wrote in message
...
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,
--
Ryan L.

Hmmm...

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

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

--
Biff
Microsoft Excel MVP


"Ryan" wrote in message
...
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


--
Ryan L.


"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"Ryan" wrote in message
...
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,
--
Ryan L.

Thanks a lot
--
Ryan L.


"Teethless mama" wrote:

Try this:
None array enter

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

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



"Ryan" wrote:

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,
--
Ryan L.

Thanks a lot
--
Ryan L.


"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"Ryan" wrote in message
...
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,
--
Ryan L.