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

--
Biff
Microsoft Excel MVP


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