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" wrote:

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)

--
Biff
Microsoft Excel MVP


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