Counting consecutive cells in a range with blanks and displaying a specific value

[mvillanueva14]

Hi,
I am new here. Please be patient.

I wanted to count the number of instances that Matt's been late.
If there are consecutive timestamps (ex. 600-620, 620-640) that he's been late, I wanted Excel to display 1 and then I'll just sum it up. Or if Excel can do this directly, add all the instances because what I actually need is the total per person.

In this example, I would need a result of 3 instances.

Column
Matt
600-620 1 1
620-640 1 1
640-700 1
700-720 1
720-740
740-800 1

Also, please tell if the blank cells will affect the formula or if I still need to do something about them.
Thank you very much.

 

[mvillanueva14]

Thank you so much! I think you would also be able to understand my question if I show you a sheet. It's so difficult explaining it! :P
Thank you, I will upload the file asap! :D

 

[mvillanueva14]

Thank you so much! I think you would also be able to understand my question if I show you a sheet. It's so difficult explaining it! :P
Thank you, I will upload the file asap! :D

You are right, ace19852...I think what I want is sooooo complicated than what I initially thought.
Here's the file.
Download Sample Data2.xlsx from Sendspace.com - send big files the easy way

Ultimately, I want to get the results highlighted in green. But then I noticed, that whole thing is only for one day. T_T
It's because if that employee has 4 "instances" of late, a memo will be issued to him already. Whether those "instances" happened within a day or within two days, etc.
And I would also need to get the monthly data. (Preferably also the daily and weekly for all 200 employees).
If there are too many things happening and excel cannot do this, I would understand. T_T

Thank you so very much for your help!

 

[ace19852]

Ok I'm sending the file back modified with a formula in cells C16 and F16 that gives you the number of group automatically. Note in order for this to work in cell A2 I had to put a "1". I changed to font colour in that cell to white so it doesn't appear. I hope you do not need to use A2 for anything else but if you do we can move the "1" to another location of your choosing and change the formula to suit.

http://www.sendspace.com/file/3xbkb

Now that step is done please discuss next requirement

 

[mvillanueva14]

Ok I'm sending the file back modified with a formula in cells C16 and F16 that gives you the number of group automatically. Note in order for this to work in cell A2 I had to put a "1". I changed to font colour in that cell to white so it doesn't appear. I hope you do not need to use A2 for anything else but if you do we can move the "1" to another location of your choosing and change the formula to suit.

http://www.sendspace.com/file/3xbkb

Now that step is done please discuss next requirement

O_O I am amazed! thank you very much ace19852.
This is pretty much what I need. I guess the difficult part is that this is only good for 1 day for each employee. I'm looking at 200 employees and 30 days. T_T
What I need next is the total of these on a daily, weekly, and monthly basis.
Total of lates per person (regardless of reason).
Total of lates with valid reason.
Total of lates with invalid reasons.
Total of instances.

1. What I am planning to do though, (please tell me if this would work or if this is the right way to do it) is to just put those three totals after each employee (cells M14:M16).
2. I will copy the formula of that whole thing horizontally so that one sheet will give me one month's data.
3. Then just make totals of the things I want on the last few columns.
4. I can also put #3 above after every 7 days I guess to get my weekly data.

Now I'm thinking if Excel can handle all this data.
Maybe I can also just put the totals on a separate sheet.

But my question have been answered! Thanks you so very much! Kindly give me advice if what I'll be doing is okay. I loooove Excel... I guess I'm just lacking the necessary IQ to work this thing. :D
Thank you, thank you ace19852. Here's what I've done.
There's really just April 1st and 31st in there.

Download Sample Data2.xlsx from Sendspace.com - send big files the easy way

Yay!

 

[ace19852]

O_O I am amazed! thank you very much ace19852.
This is pretty much what I need. I guess the difficult part is that this is only good for 1 day for each employee. I'm looking at 200 employees and 30 days. T_T
What I need next is the total of these on a daily, weekly, and monthly basis.
Total of lates per person (regardless of reason).
Total of lates with valid reason.
Total of lates with invalid reasons.
Total of instances.

1. What I am planning to do though, (please tell me if this would work or if this is the right way to do it) is to just put those three totals after each employee (cells M14:M16).
2. I will copy the formula of that whole thing horizontally so that one sheet will give me one month's data.
3. Then just make totals of the things I want on the last few columns.
4. I can also put #3 above after every 7 days I guess to get my weekly data.

Now I'm thinking if Excel can handle all this data.
Maybe I can also just put the totals on a separate sheet.

But my question have been answered! Thanks you so very much! Kindly give me advice if what I'll be doing is okay. I loooove Excel... I guess I'm just lacking the necessary IQ to work this thing. :D
Thank you, thank you ace19852. Here's what I've done.
There's really just April 1st and 31st in there.

Download Sample Data2.xlsx from Sendspace.com - send big files the easy way

Yay!

Hi sounds like your on the right road however the link above is to the original file not the new file. Can you check if you want me to have a look.

Many Thanks

 

[mvillanueva14]

Hi sounds like your on the right road however the link above is to the original file not the new file. Can you check if you want me to have a look.

Many Thanks

Thank you so much for your help ace19852! and I'm sorry again, for sending you the wrong file. Here's what I did:
Download Sample Data3.xlsx from Sendspace.com - send big files the easy way

 

[ace19852]

Hi sounds like your on the right road however the link above is to the original file not the new file. Can you check if you want me to have a look.

Many Thanks

I had a look at your sheet and I know this isn't dealing with the question you have asked but consider this:

Put this in V27
=IF(SUM(V18:V26)<>0,COUNTA(V18:V26)-SUM(V18:V26),COUNTA(V18:V26)) and

Put this in V28
=IF(COUNTA(V18:V26)-SUM(V18:V26)=0,COUNTA(V18:V26),IF(COUNTA(V18:V26)-SUM(V18:V26)<>0,SUM(V18:V26),""))

Now if a "1" appears in column V then count the number of valid reasons and if you use "0" to indicate invalid reason it count the invalid.

Like computers 1 = on 0 = off

Anyway the idea to to reduced the number of columns which always help.

So column V now becomes a Valid/Invalid column thereby getting rid of "U" in this instance but if applied to the whole sheet you reduce the number of column by 10. If you like it rearrange the sheet and resend if not we go back to your original questions

Cheers