Conditional maximum

[Jai]

Hi. I have a sheet showing the number of classrooms for a range of schools.
It has school names in column A and the number of the classroom in column B,
eg.

School Classroom number
Sydney Primary School 1
Sydney Primary School 2
Sydney Primary School 3
Sydney Primary School 4

London School 1
London School 2
London School 3

New York School 1
New York School 2
New York School 3
New York School 99
New York School 99


I want to have a column that shows the number of classrooms for each school.
Sydney=4 and London=3 for example.

Further complicating matters is that some schools have a 99 for classroom
number after the initial number sequence. This represents an off location
room. There may be multiple 99's. I do not want to count these values. So New
York above would be 3.

Have tried using max functions but with no success. Any suggestions? Thanks
in advance.

 

[T. Valko]

One way...

Try this array formula** :

=MAX(IF((A2:A100="New York School")*(B2:B100<99),B2:B100))

Or, use a cell to hold the school name:

D2 = Sydney Primary School

=MAX(IF((A2:A100=D2)*(B2:B100<99),B2:B100))

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

 

[Jai]

Didn't work when I tried to use it as an array over the whole range but did
when I used it as an array on each cell. Thanks

 

[T. Valko]

Didn't work when I tried to use it as an array over

the whole range but did when I used it as an array
on each cell.

Not sure what that means but if by "whole range" you mean an entire column
then no, it wouldn't work because an array formula can't reference an entire
column (unless you're using Excel 2007).