Maximum and Minimum with Complex Criteria

[manoshde]

Hello


I hope i am not duplicating any previous query.


I am trying to generate a minimum and maximum summary for a list that
includes similar logical labels.


For example


If my list is


Community Park.....10ha
District Park...20ha
Canal..3ha
Open Space..4ha
Lake...1ha


I want to find the min/ max of all types of Green spaces (Community
Park, District Park and Open Space) and Water Bodies (Lake, Canal).


Is there an easy way to do this without adding another column?


For example one could add a col that identifies Community Park,
District Park and Open Space as Green Spaces and search the min/ max in

this but as my list is long and needs frequent updates it would be a
pain to maintain.


Thanks in advance.


Manosh

 

[Bob Phillips]

A few assumptions made, but try

=MAX(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10))

as an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Manosh]

What would the formula for minimum be?
I tried to replace Max wih min but it doesnt work.
Would appreciate an answer on this,
regards
m

 

[Bob Phillips]

It worked for me.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bernie Deitrick]

=MIN(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10,MAX(C1:C10)))

Also entered using Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP

 

[Harlan Grove]

=MIN(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),C1:C10,MAX(C1:C10)))

Also entered using Ctrl-Shift-Enter.

....

Or

=-MAX(IF(ISNUMBER(FIND({"Park","Space"},A1:A10)),-C1:C10))