Multi-Criteria Crisis

[Odikira]

Hello Mr. Excel members. I am turning to you for help and I hope I don't confuse you.

SO, here is the situation. We have data that is appended to a spreadsheet daily and now we need to have a very simple count done, but it turns out to not be so simple. Here is a sample of the data:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]CompletedDate[/TD]
[TD]ReleaseTypeCode[/TD]
[TD]Tower[/TD]
[TD]Type[/TD]
[TD]CompletedTime[/TD]
[TD]ReleaseTime[/TD]
[TD]ReleaseDate[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]4/10/2016[/TD]
[TD]RO[/TD]
[TD]PSGT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]21:32[/TD]
[TD]4/28/2017[/TD]
[TD]=SUMPRODUCT(--(WEEKDAY(G2:G11,2)>5))[/TD]
[/TR]
[TR]
[TD]4/10/2016[/TD]
[TD]RO[/TD]
[TD]PSGT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]21:32[/TD]
[TD]4/28/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/11/2016[/TD]
[TD]RO[/TD]
[TD]PSGT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]21:32[/TD]
[TD]4/28/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/11/2016[/TD]
[TD]RO[/TD]
[TD]PSGT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]04:45[/TD]
[TD]4/29/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/3/2016[/TD]
[TD]RO[/TD]
[TD]PA&B[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]10:00[/TD]
[TD]4/27/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/4/2016[/TD]
[TD]RO[/TD]
[TD]PA&B[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]10:00[/TD]
[TD]4/27/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/3/2016[/TD]
[TD]RO[/TD]
[TD]PSGT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]04:45[/TD]
[TD]4/29/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/3/2016[/TD]
[TD]RO[/TD]
[TD]PCENT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]15:55[/TD]
[TD]4/6/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/6/2016[/TD]
[TD]RO[/TD]
[TD]PCENT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]15:55[/TD]
[TD]4/6/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8/30/2016[/TD]
[TD]RO[/TD]
[TD]PSGT[/TD]
[TD]5[/TD]
[TD]1/2/00[/TD]
[TD]07:23[/TD]
[TD]4/29/2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to do is count how many records have a release date that is on the weekend and how many are after or outside business hours (08:00-18:00). So far I am able to get a count with a static range (G2-G11), but the problem is our data is added to each day. We would like to have it be a little more dynamic. The other thing is I have been unsuccessful in counting a cell if it's outside business hours, and not have it double if it's outside the business hours and on the weekend. Any help you can provide would be greatly appreciated. So the three things I need help with,

1 - Count how many rows have a release date that is on a weekend.
2 - Count how many rows have a release time outside business hours (08:00-18:00).
3 - Dynamic range to include new data added daily (this isn't a priority, but would be helpful).

Thank you for all your help!

 

[mart37]

For a dynamic range you can use a named range with OFFSET.
When you format your table with FORMAT AS TABLE then Excel use automatic dynamic ranges!!!!

Example with OFFSET:

ReleaseTime : =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F:$F)-1,1)
ReleaseDate : =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1)

Count outside worktime and outside weekend:
=SUMPRODUCT(--(((ReleaseTime<8/24)+(ReleaseTime>18/24))*(WEEKDAY(ReleaseDate,2)<6)))