Count within a Range Q

[Sean]

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

 

[Bob Phillips]

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]

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)

 

[Mike H]

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

 

[Mike H]

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]

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"

 

[Bob Phillips]

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]

Bingo, thanks Bob

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

 

[Bob Phillips]

=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]

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))))=""))

 

[Bob Phillips]

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)



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))))=""))