Counting Unique Empty Cells of a Range

[IronDogg]

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:

\1

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?

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[IronDogg]

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, S=Stat Days Off. 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, and Stat Days off, are included in counts. Hopefully that helps explain my problem better? :oops:

So, the first row, has 2 days off, followed by 4 day shifts, followed by 2 days off, followed by 4 night shifts, followed by 4 days off, followed by 4 Day Shifts, followed by 5 days off, followed by.... etc...

P.S. There should be a black border line between those M's. It is not one big cell, but two... :oops:

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[IronDogg]

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

Yes, and actually, this shift is not a year round thing either, it actually is from Oct29 to Mar31, so the range I am using is D5:FA5. Sorry for that lack of specific info again.

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

Wow! Awesome formula! Just when I was thinking that I may finally be getting a grasp on excel, you lay that one out, and make me think that I need to go back to school for more excel courses...
The formula works, but is not entirely accurate, and I don't know why...
Here is my adjusted formula from yours:

SUM(1*((LARGE(IF((D5:FA5="")*(E5:FB5<>D5:FA5),COLUMN( D5:FA5),0),{1,2,3,4,5,6,7,8,9})-LARGE(IF((E5:FB5="")*(E5:FB5<>D5:FA5),COLUMN(E5:FB5),1 ),{1,2,3,4,5,6,7,8,9}
)+1)=2))

Now even though, this formula does not take into account the "S" days (stat holidays), it still is miscounting the number of sets of 2 days off in a row for no easily apparent reason. Would a "bigger example" of my spreadsheet be easier to decipher? I know I am not very good at describing my problems...

I have also been trying to figure out a formula that will show how many "weekend days" (Saturday's and Sunday's - Grayed out cells), each shift has to work...

I am not sure how much time you want to spend helping me with this, but if you are so inclined, I could email you my spreadsheet, so you could better relate to my dilemna. Also, send me your paypal address if you would accept a "tip" of some kind...

[Newbie]

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

[IronDogg]

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?

Yes, that is kinda what the above formula does already, except it 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 just the number of "O's", but rather the number of groups of JUST 2 concurrent "O's", nothing more. So in the end, wouldn't the formula be 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 to the final printed product. Less "busy" that way, maybe...

[Tom Ogilvy]

If you want me to adjust the formula to those particulars, then send me a
workbook with a typical sheet with data. Mark where you want the summary
data (what columns).

Make sure your email address has a valid return address so I can send it
back.

[email protected]

--
Regards,
Tom Ogilvy

"IronDogg" <[email protected]> wrote in
message news:[email protected]...
>
> Tom Ogilvy Wrote:
> > Your first problem is that Excel only has 256 columns, so it won't
> > support
> > 365/6 days.
>
> Yes, and actually, this shift is not a year round thing either, it
> actually is from Oct29 to Mar31, so the range I am using is D5:FA5.
> Sorry for that lack of specific info again.
>
> Tom Ogilvy Wrote:
> >
> >
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))
>
> Wow! Awesome formula! Just when I was thinking that I may finally be
> getting a grasp on excel, you lay that one out, and make me think that
> I need to go back to school for more excel courses...
> The formula works, but is not entirely accurate, and I don't know
> why...
> Here is my adjusted formula from yours:
>
> SUM(1*((LARGE(IF((D5:FA5="")*(E5:FB5<>D5:FA5),COLUMN(
>
D5:FA5),0),{1,2,3,4,5,6,7,8,9})-LARGE(IF((E5:FB5="")*(E5:FB5<>D5:FA5),COLUMN
(E5:FB5),1
> ),{1,2,3,4,5,6,7,8,9}
> )+1)=2))
>
> Now even though, this formula does not take into account the "S" days
> (stat holidays), it still is miscounting the number of sets of 2 days
> off in a row for no easily apparent reason. Would a "bigger example" of
> my spreadsheet be easier to decipher? I know I am not very good at
> describing my problems...
>
> I have also been trying to figure out a formula that will show how many
> "weekend days" (Saturday's and Sunday's - Grayed out cells), each shift
> has to work...
>
> I am not sure how much time you want to spend helping me with this, but
> if you are so inclined, I could email you my spreadsheet, so you could
> better relate to my dilemna. Also, send me your paypal address if you
> would accept a "tip" of some kind...
>
>
> --
> IronDogg
> ------------------------------------------------------------------------
> IronDogg's Profile:
http://www.excelforum.com/member.php...o&userid=33291
> View this thread: http://www.excelforum.com/showthread...hreadid=531171
>