How to count date range while excluding dates

[alm395]

Hi,

I have a storm report that we have been running since 2/1/19 that basically tracks the number of incidents that occur each day.

This is a sample from the report (top row is the column they are in):[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]G[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Time frame[/TD]
[TD]Month[/TD]
[TD]Blue Sky / Storm[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl80, width: 125"]2/1/2019 09:50:14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl79, width: 85"]2:39:08[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl80, width: 130"]6/6/2019 21:39:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl79, width: 85"]0:24:55[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl81, width: 130"]6/7/2019 00:06:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl79, width: 85"]1:04:59[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl81, width: 125"]6/9/2019 07:42:38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl79, width: 85"]1:50:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl80, width: 125"]6/27/2019 18:31:45[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl79, width: 85"]0:33:41[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]6[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

I have one cell in the report that needs to count the total of orders for the month, another for the total of orders for the year, and another cell counting the totals if it meets a criteria:

Total for June Blue Sky:=COUNTIFS(Total_Data_Combined!$T:$T,MONTH(B1),Total_Data_Combined!$U:$U,"B")

Total for June Storm:=COUNTIFS(Total_Data_Combined!$T:$T,MONTH(B1),Total_Data_Combined!$U:$U,"S")

Total June if <1:00:00 during Storm: =COUNTIFS(Total_Data_Combined!$T:$T,MONTH(B1),Total_Data_Combined!$U:$U,"S")

Total YTD Storm: =COUNTIFS(Total_Data_Combined!$T:$T,MONTH(B1),Total_Data_Combined!$U:$U,"S",Total_Data_Combined!$M:$M,"<"&TIME(1,0,0))

Now we want to continue to track the same way that we have, but to exclude the dates of 6/6/2019 - 6/9/2019. How do I incorporate this in these formulas?

Thanks so much!

 

[Marcelo Branco]

Maybe something like this

Total: excluding 6/6/2019 to 6/9/2019 in G:G; T:T = Month (B1); U:U = "B"
=COUNTIFS(Total_Data_Combined!G:G,"<"&DATE(2019,6,6),Total_Data_Combined!T:T,MONTH(B1),Total_Data_Combined!U:U,"B")+COUNTIFS(Total_Data_Combined!G:G,">="&DATE(2019,6,10),Total_Data_Combined!T:T,MONTH(B1),Total_Data_Combined!U:U,"B")

M.

 

[alm395]

Maybe something like this

Total: excluding 6/6/2019 to 6/9/2019 in G:G; T:T = Month (B1); U:U = "B"
=COUNTIFS(Total_Data_Combined!G:G,"<"&DATE(2019,6,6),Total_Data_Combined!T:T,MONTH(B1),Total_Data_Combined!U:U,"B")+COUNTIFS(Total_Data_Combined!G:G,">="&DATE(2019,6,10),Total_Data_Combined!T:T,MONTH(B1),Total_Data_Combined!U:U,"B")

M.

Awesome!!! Worked like a charm!

 

[Marcelo Branco]

You are welcome. Glad to help

M.