Count within a Range Q
 

Would it be possible to count the number of empty cells in a column,
that fall between the first occupied cell and the last occupied cell
in a range.

For example if A5:A20 can be populated, but the first occupied cell is
A8 and the last occupied cell is A12, I wish to count only those cells
that are <blank between A8:A12

Another example would be if the first occupied cell was A13 and the
last occupied cell is A15, I wish to count only those cells that are
<blank between A13:A15

If you could visualise the above being Clock in / Out times, I wish to
know what part of the day there is no job allocated

Thanks

Count within a Range Q
 

Here is a nice simple formula

=SUM(--((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))):INDEX(A5:A20,MAX(IF(A5:A20< "",ROW(A5:A20)-MIN(ROW(A5:A20))+1))))<""))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sean" wrote in message
...
Would it be possible to count the number of empty cells in a column,
that fall between the first occupied cell and the last occupied cell
in a range.

For example if A5:A20 can be populated, but the first occupied cell is
A8 and the last occupied cell is A12, I wish to count only those cells
that are <blank between A8:A12

Another example would be if the first occupied cell was A13 and the
last occupied cell is A15, I wish to count only those cells that are
<blank between A13:A15

If you could visualise the above being Clock in / Out times, I wish to
know what part of the day there is no job allocated

Thanks

Count within a Range Q
 

Thanks Bob, I'm getting a value 2 returned, the answer I am expecting
is 3

I have A15 populated and A19, thus A16:A18 are <blank, so answer
should be 3 (everything else is blank also in A)

Count within a Range Q
 

Try this function. Put it in a module. Alt +F11 to open VB editor, rght click
'this workbook' insert module and paste it in

Function countblanks(range As range)
rangesize = range.Cells.Count
For Each c In range
If IsEmpty(c) Then
Count = Count + 1
x = x + 1
Else
x = x + 1
If x < rangesize Then
Count = 0
End If
End If
Next
countblanks = Count
End Function

Call with
=countblanks(a5:a20)
Change the range to suit

Mike

"Sean" wrote:

Thanks Bob, I'm getting a value 2 returned, the answer I am expecting
is 3

I have A15 populated and A19, thus A16:A18 are <blank, so answer
should be 3 (everything else is blank also in A)

Count within a Range Q
 

Sean

Much simpler and provided there aren't multiple blocks of unpopulated cells
in the range then this should work, unlike the other effort which didn't!!

Function countblanks(range As range)
For Each c In range
If IsEmpty(c) Then
Count = Count + 1
End If
Next
countblanks = Count
End Function

Mike

"Sean" wrote:

Thanks Bob, I'm getting a value 2 returned, the answer I am expecting
is 3

I have A15 populated and A19, thus A16:A18 are <blank, so answer
should be 3 (everything else is blank also in A)

Count within a Range Q
 

Thanks Mike, that will count the blanks in the entire "master range",
but not those specifically between the first range I populate in the
"master range" and the last cell I populate in the "master range"

Count within a Range Q
 

Sorry Sean, I counted the non-blanks

=SUM(--((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))):INDEX(A5:A20,MAX(IF(A5:A20< "",ROW(A5:A20)-MIN(ROW(A5:A20))+1))))=""))

still an array formula

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sean" wrote in message
...
Thanks Bob, I'm getting a value 2 returned, the answer I am expecting
is 3

I have A15 populated and A19, thus A16:A18 are <blank, so answer
should be 3 (everything else is blank also in A)

Count within a Range Q
 

Bingo, thanks Bob

One slight tweak, if all cells in Range A5:A20 are blank/empty, how
would I tweak to return " "

Count within a Range Q
 

=IF(COUNTA(A5:A20)=0,"",SUM(--((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))):INDEX(A5:A20,MAX(IF(A5:A20< "",ROW(A5:A20)-MIN(ROW(A5:A20))+1))))="")))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sean" wrote in message
...
Bingo, thanks Bob

One slight tweak, if all cells in Range A5:A20 are blank/empty, how
would I tweak to return " "

Count within a Range Q
 

Thanks Bob a neater take on my one which was

=if(=SUM(--((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-
MIN(ROW(A5:A20))+1))):I*NDEX(A5:A20,MAX(IF(A5:A20< "",ROW(A5:A20)-
MIN(ROW(A5:A20))+1))))=""))=15,0,=SUM(--
((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))):I*
NDEX(A5:A20,MAX(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))))=""))

Count within a Range Q
 

Yeah, it's a bit simpler to count a blank range than the populated cells in
a block <bg

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Sean" wrote in message
...
Thanks Bob a neater take on my one which was

=if(=SUM(--((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-
MIN(ROW(A5:A20))+1))):I*NDEX(A5:A20,MAX(IF(A5:A20< "",ROW(A5:A20)-
MIN(ROW(A5:A20))+1))))=""))=15,0,=SUM(--
((INDEX(A5:A20,MIN(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))):I*
NDEX(A5:A20,MAX(IF(A5:A20<"",ROW(A5:A20)-MIN(ROW(A5:A20))+1))))=""))