A sheet can have a variable range of adjacent cells each containing
the value =1
The range defines the perimeter of, or surrounds a group of empty
cells however outside the perimeter, the remaining cells on the sheet
are empty as well.

The task is to count the empty cells that are surrounded by the
perimeter of 1’s:

To do this manually, I use the @countif (range=0) function however it
requires that I go into the worksheet and select the range manually.

I would like to do this by formula or macro.

The knowns are:
1) The location of the start/end cell of the range
2) That the next cell in the range will be above, below, left or
right of its neighbour but we never know which.
3) I think to define the range, I need to to search for the location
of all cells having the value of 1

IF: Your corner of the 'perimeter' is known, and your 1's are only on a
rectangular perimeter, then you could use the formula

=COUNTBLANK(OFFSET(C3,0,0,COUNT(C:C),COUNT(3:3)))

where C3 is your upper left corner.

If your perimeter snakes around, then you would require VBA, and some fairly
complex coding.

What exactly is it that you are trying to do?

HTH,
Bernie
MS Excel MVP

"Rosehill - ExcelForums.com" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
>A sheet can have a variable range of adjacent cells each containing
> the value =1
> The range defines the perimeter of, or surrounds a group of empty
> cells however outside the perimeter, the remaining cells on the sheet
> are empty as well.
>
> The task is to count the empty cells that are surrounded by the
> perimeter of 1’s:
>
> To do this manually, I use the @countif (range=0) function however it
> requires that I go into the worksheet and select the range manually.
>
> I would like to do this by formula or macro.
>
> The knowns are:
> 1) The location of the start/end cell of the range
> 2) That the next cell in the range will be above, below, left or
> right of its neighbour but we never know which.
> 3) I think to define the range, I need to to search for the location
> of all cells having the value of 1
>

As you say, the perimeter more often than not snakes around and is no
an orderly rectangle so the manual process is actuall
countblank(range 1)+countblank (range 2) etc until all of the cell
are identified

I envisage that VB code to do this would involve offseting (2,1) fro
start /finish cell. This would identify the first blank cell in th
irregular range that is now surrounded by conceivably up to 8 blan
cells. Getting this far in code and even counting the blank cell
around the offset is no problem for me but then writing code to mov
through the range that is bounded by the perimeter of 1's an
identify all of the blank cells is the problem. Yes I think the cod
is complex. Its kind of like a search, identify and count missio
where the boundry of the search is a perimeter of ones

Rosehil