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.

 

[ace19852]

Assuming the timestamps are in column A and the 1's are in columns B and C then if you want to count all the 1's in Cols B and C used this formula in array form

{=COUNT(B3:C8)}

remember to make an array formula type into the desired cell:

=COUNT(B3:C8)

and before hitting enter hold down Ctrl and Shift together and then hit the enter key and it will then appear in {} brackets.

Cheers

 

[mvillanueva14]

Assuming the timestamps are in column A and the 1's are in columns B and C then if you want to count all the 1's in Cols B and C used this formula in array form

{=COUNT(B3:C8)}

remember to make an array formula type into the desired cell:

=COUNT(B3:C8)

and before hitting enter hold down Ctrl and Shift together and then hit the enter key and it will then appear in {} brackets.

Cheers

Thank you very much ace19852, It worked well. But I'm sorry I didn't specify more. I also wanted to still count it even if the data does not appear in consecutive cells. You were right in your assumptions about the columns. Sorry I'm receiving an error message when I try to download the MrExcel HTML Maker.

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

I wanted to count A3:A5 as 1 instance, A8 as another, B3:B4 as another, and B6 as another. And then I will just add them up to sum all instances.

Thank you very much, I really appreciate it.

 

[mvillanueva14]

Up. Sorry, need help badly.

 

[ace19852]

I'm a bit confused with what you are doing. You say you want to count A3:A5 which =count(A3:A5) would give 0, =counta(A3:A5) would give 3

So as you are using 1's you can use =count(....) which sums the cells in a column or row.

However if you have e.g.A3 to A5 which is a time stamp and text and don't want to sum the cells be just count the number of cells with something in it use =counta(....).

With this info can you apply it to your sheet yourself.

If I completely misunderstanding you please rephrase request

Cheers

 

[mvillanueva14]

Yes, my mistake, I'm so sorry. From the above sample, I meant I would like to count B3 to B5 (which has "1" in each cell) as one instance. So it's not the timestamp, it's the "1"s I'm after. I want B8 to be counted as another instance, C3:C4 as another and finally, C6. You're formula earlier was actually what I was looking for except that when I put a "1" in, let's say B8 (with B7 and B9 left blank), the formula doesn't count B8 as an instance.

Thank you very much! I hope I am clearer this time.

 

[ace19852]

Yes, my mistake, I'm so sorry. From the above sample, I meant I would like to count B3 to B5 (which has "1" in each cell) as one instance. So it's not the timestamp, it's the "1"s I'm after. I want B8 to be counted as another instance, C3:C4 as another and finally, C6. You're formula earlier was actually what I was looking for except that when I put a "1" in, let's say B8 (with B7 and B9 left blank), the formula doesn't count B8 as an instance.

Thank you very much! I hope I am clearer this time.

So just specifically count B3 to B5 + B8 + C3 to C4 + C6 as a formula would be

=COUNT(B3:B5)+B8+COUNT(C3:C4)+C6

However as I don't understand your need I find this to be a strange request

Cheers

 

[mvillanueva14]

Hi, thank you again for your help. I'm sorry for not making it clear. Can't seem to do it without the excel sheet and I still cannot download the HTML Maker. I hope I can make it clearer this time. Column A has the list of names of the employees. (I am handling 200). Under each name are the time stamps from 6am to 12mn, divided into twenty minute durations. 600-620, 620-640, 640-700, etc. (Should I just put the time stamps in Column B?) The heading (first cell) of the next few columns will have the label "Reason 1, Reason 2, etc" until Reason 8. Some of these reasons are valid, and some are invalid reasons for being late. My problem is that I want to get these results:
1. Total number of missed time stamps. (Meaning employee was late. I was thinking of putting "1", like my previous sample, because I needed to add them all later. Or is this right?)
2. Total number of missed time stamps with valid/invalid reasons. (This I think I can do, already.)
3. Total number of instances when employee was late. This "instance" part is for issuing memos. There are conditions though... a. If the employee is late in any of the 20 minute durations, then that is counted as one instance. If the employee is late consecutively (no matter if its for 40 minutes or 8 hours), then that will still be counted as one instance only. If, in a day, the employee comes in late for one 20-minute duration, then again for two consecutive 20-minute durations, then I want to get a result of a total of 3 lates, but 2 "instances" only.

Your formula is again, the one I'm looking for. But that means I have to have the data first before I can apply the formula right? The problem though is that what I'm making is a sort of tracker wherein the total should be seen real-time as the managers enter the data. (Wherein data refers to the "1").

Thank you again so much for your help. You're the only one answering my question here. T_T
Thank you!

 

[mvillanueva14]

Oh no, I think I'm not including everything here. Please let me think about it first. Sorry.

 

[ace19852]

I'm think I see what you ultimately want to achieve.

Try uploading your excel book to say https://www.sendspace.com/ and put the link here so I can see the file .

This is the problem working remotely and often people just to not phrase their needs exactly. I can understand the difficulty and to be truthful if the question is well phrased with an interesting issue more people are inclined to answer.

Your original question did seem to be simple which is why you got me as I'm fairly new to this forum.

However once we get to your actual requirement I believe you will have quite an interesting question.

But as NLP teaches keep asking til you get the responses you need Tony Robbins would be proud