Counting Unique Empty Cells of a Range
 

Hello all, I have a question regarding the count functions with excel. I
am wondering how to count sets of "unique" empty cells within a range?
Take the example below:

[image: http://doggpoundclan.com/images/excelexample01.jpg]

Within a row, I would like a count of how many instances of "2 empty
cells in a row", and how many instances of "3 empty cells in a row",
and how many instances of "4 empty cells in a row", etc, etc.

So in the first row, there are 2 instances of "2 empty cells in a row",
1 instance of "3 empty cells in a row", and 1 instance of "5 empty cells
in a row".

The second row has 3 instances of "2 empty cells in a row", and 2
instances of "3 empty cells in a row".

How would I go about counting all the sets of empty cells and defining
them in a table?


--
IronDogg
------------------------------------------------------------------------
IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291
View this thread: http://www.excelforum.com/showthread...hreadid=531171

Counting Unique Empty Cells of a Range
 

It isn't clear to me how you got your counts based on the picture you
referenced. So you would have to explain that. Atre cells counted more
than once and gray cells excluded?

do you want to do this with a macro or with worksheet formulas

Do you want counts by row or a sum over all rows.

--
Regards,
Tom Ogilvy


"IronDogg" wrote in
message ...

Hello all, I have a question regarding the count functions with excel. I
am wondering how to count sets of "unique" empty cells within a range?
Take the example below:

[image: http://doggpoundclan.com/images/excelexample01.jpg]

Within a row, I would like a count of how many instances of "2 empty
cells in a row", and how many instances of "3 empty cells in a row",
and how many instances of "4 empty cells in a row", etc, etc.

So in the first row, there are 2 instances of "2 empty cells in a row",
1 instance of "3 empty cells in a row", and 1 instance of "5 empty cells
in a row".

The second row has 3 instances of "2 empty cells in a row", and 2
instances of "3 empty cells in a row".

How would I go about counting all the sets of empty cells and defining
them in a table?


--
IronDogg
------------------------------------------------------------------------
IronDogg's Profile:
http://www.excelforum.com/member.php...o&userid=33291
View this thread: http://www.excelforum.com/showthread...hreadid=531171

Counting Unique Empty Cells of a Range
 

Tom Ogilvy Wrote:
It isn't clear to me how you got your counts based on the picture you
referenced. So you would have to explain that. Atre cells counted
more
than once and gray cells excluded?

do you want to do this with a macro or with worksheet formulas

Do you want counts by row or a sum over all rows.

--
Regards,
Tom Ogilvy


Okay, the picture shows a work shift schedule, with a Sunday through
Saturday work week. The gray cells are Saturday, Sunday. D=Days,
N=Nights, M=mornings. Cells with nothing in them are days off. What I
am looking for is a formula at the end of each row that will calculate
how many "2 Day off"-sets of days off there were in an entire years
shift. Then another cell with a formula showing how many "3 Days
off"-sets of days off there were in an entire years shift, then 4 days,
then 5 days, then any sets larger than 5 days... Gray cells are included
in counts. Does that help explain my problem better?


--
IronDogg
------------------------------------------------------------------------
IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291
View this thread: http://www.excelforum.com/showthread...hreadid=531171

Counting Unique Empty Cells of a Range
 

Your first problem is that Excel only has 256 columns, so it won't support
365/6 days.

anyway,
SUM(1*((LARGE(IF((D8:L8="")*(E8:M8<D8:L8),COLUMN( D8:L8),0),{1,2,3,4,5,6,7,8
,9})-LARGE(IF((E8:M8="")*(E8:M8<D8:L8),COLUMN(E8:M8),1 ),{1,2,3,4,5,6,7,8,9}
)+1)=2))

entered with Ctrl+Shift+Enter should indicate the number of 2 successive
blanks in the range D8:M8. I think you have to have a non empty cell at
each end so you might need to put in a dummy column at each end and fill it
with a value.

the arrays 1,2,3,4,5,6,7,8,9 must be numbered high enough to cover the total
number of blank ranges in the row.

D8:M8 was just a testing range. You can adjust if for a larger section of a
row by using a similar pattern.

--
Regards,
Tom Ogilvy

"IronDogg" wrote in
message ...

Tom Ogilvy Wrote:
It isn't clear to me how you got your counts based on the picture you
referenced. So you would have to explain that. Atre cells counted
more
than once and gray cells excluded?

do you want to do this with a macro or with worksheet formulas

Do you want counts by row or a sum over all rows.

--
Regards,
Tom Ogilvy


Okay, the picture shows a work shift schedule, with a Sunday through
Saturday work week. The gray cells are Saturday, Sunday. D=Days,
N=Nights, M=mornings. Cells with nothing in them are days off. What I
am looking for is a formula at the end of each row that will calculate
how many "2 Day off"-sets of days off there were in an entire years
shift. Then another cell with a formula showing how many "3 Days
off"-sets of days off there were in an entire years shift, then 4 days,
then 5 days, then any sets larger than 5 days... Gray cells are included
in counts. Does that help explain my problem better?


--
IronDogg
------------------------------------------------------------------------
IronDogg's Profile:
http://www.excelforum.com/member.php...o&userid=33291
View this thread: http://www.excelforum.com/showthread...hreadid=531171

Counting Unique Empty Cells of a Range
 

Assuming this is a rota, could you not fill in the blanks with 'O' for Off,
for example, then count the O's using And offset reference capabilities?

"IronDogg" wrote:


Hello all, I have a question regarding the count functions with excel. I
am wondering how to count sets of "unique" empty cells within a range?
Take the example below:

[image: http://doggpoundclan.com/images/excelexample01.jpg]

Within a row, I would like a count of how many instances of "2 empty
cells in a row", and how many instances of "3 empty cells in a row",
and how many instances of "4 empty cells in a row", etc, etc.

So in the first row, there are 2 instances of "2 empty cells in a row",
1 instance of "3 empty cells in a row", and 1 instance of "5 empty cells
in a row".

The second row has 3 instances of "2 empty cells in a row", and 2
instances of "3 empty cells in a row".

How would I go about counting all the sets of empty cells and defining
them in a table?


--
IronDogg
------------------------------------------------------------------------
IronDogg's Profile: http://www.excelforum.com/member.php...o&userid=33291
View this thread: http://www.excelforum.com/showthread...hreadid=531171

Counting Unique Empty Cells of a Range
 

Newbie Wrote:
Assuming this is a rota, could you not fill in the blanks with 'O' fo
Off,
for example, then count the O's using And offset referenc
capabilities?


Yes, that is kinda what the above formula does already, except i
counts blanks instead of O's. I already use a formula: =COUNTIF(D5:FA5
"N") to count number of nights, etc. So with the "O's", it is not jus
the number of "O's", but rather the number of groups of JUST
concurrent "O's", nothing more. So in the end, wouldn't the formula b
very similiar to the one Tom provided? Or am I missing something there
regarding the offset reference capabilities?

Also, to do that way, I would prefer if the "O's" could be invisible t
the final printed product. Less "busy" that way, maybe... :

--
IronDog
-----------------------------------------------------------------------
IronDogg's Profile: http://www.excelforum.com/member.php...fo&userid=3329
View this thread: http://www.excelforum.com/showthread.php?threadid=53117