Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a formula on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4) or to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the visible date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.

this is an array formula so must be entered/edited with ctrl+shift+enter

=MAX(IF(B1:B7="abc",C1:C7))

--
Don Guillett
SalesAid Software

"rmellison" wrote in message
...
Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a formula
on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4) or
to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the visible
date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.

You can use autofilter and filter on abc, then use the formula

=SUBTOTAL(4,B2:B100)

will retun the max value of the filtered item, when in this case the
values are in B2:B100

Regards,

Peo Sjoblom

"rmellison" wrote in message
...
Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a formula
on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4) or
to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the visible
date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.

I had tried that previously, array-entered too, but had got #NUM as my error
message. Problem was that I was using the entire column as the reference (B:B
etc) so it was including the header (which is a charater string). Changed the
reference and hey presto! Many thanks for your help.

"Don Guillett" wrote:

this is an array formula so must be entered/edited with ctrl+shift+enter

=MAX(IF(B1:B7="abc",C1:C7))

--
Don Guillett
SalesAid Software

"rmellison" wrote in message
...
Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a formula
on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4) or
to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the visible
date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.

This also works well. SUBTOTAL looks like quite a useful function for
filtered lists. Thanks!

"Peo Sjoblom" wrote:

You can use autofilter and filter on abc, then use the formula

=SUBTOTAL(4,B2:B100)

will retun the max value of the filtered item, when in this case the
values are in B2:B100

Regards,

Peo Sjoblom

"rmellison" wrote in message
...
Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a formula
on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4) or
to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the visible
date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.

I should have told you that it didn't work with full columns.

--
Don Guillett
SalesAid Software

"rmellison" wrote in message
...
I had tried that previously, array-entered too, but had got #NUM as my
error
message. Problem was that I was using the entire column as the reference
(B:B
etc) so it was including the header (which is a charater string). Changed
the
reference and hey presto! Many thanks for your help.

"Don Guillett" wrote:

this is an array formula so must be entered/edited with ctrl+shift+enter

=MAX(IF(B1:B7="abc",C1:C7))

--
Don Guillett
SalesAid Software

"rmellison" wrote in message
...
Hi there Excel Gurus.

I have a long list of data in two columns, and I want to write a
formula
on
a different sheet that finds the maximum of all values in column 2 that
correspond to a given character string in column 1. For example.

abc 3
bcd 5
cde 7
abc 2
def 4
abc 4
bcd 8

I need to find the maximum of all values corresponding to abc (ie. 4)
or
to
bcd (ie. 8) where I can enter the required string in a cell next to my
formula cell. Have tried filtering but if you use =MAX( ) on the
visible
date
it still uses the entire range.

Hope you get what I mean. Thanks in advance.