COUNTIF Dates With CountBlank Formula.

Hi all!
I need your help to write formulas in excel for these 4 KPI (Active, New, Closed, and Unresolved cases).


I need to write the formulas in excel (see the question). Here's the explanation for each KPI:

1. Active Cases (Hard)

*Hint: Solve the New and Closed cases first. Then, it will make it easier to find active cases.
image.png
2. New Cases: (Easy)

Jacob_S1585_0-1673052806487.png
3. Closed Cases: (Easy)

Jacob_S1585_1-1673052872575.png
4. Unresolved cases (Moderate)

Jacob_S1585_2-1673052891859.png
Q.

1. Write excel formulas for Active Cases (make sure to write the formulas for the intermediate steps highlighted in blue, green and orange)

2. Write excel formulas for New Cases

3. Write excel formulas for Closed Cases

4. Write excel formulas for Unresolved cases (make sure to write the formulas for the intermediate steps highlighted in green and yellow)



*NOTE:

1. Please disregard TF 1 and solve TF 2.

2. TF = "Time Frame". For TF2, Please select 1/1/2022 (start date) - 11/1/2022 (end date)

3. The expected result will be:
ACTIVE CASES: 10
NEW: 5
CLOSED: 3
UNRESOLVED: 5

Wondering if someone can take a look and help me with this. I already tried my best and got no luck. I got: (see excel file)
What's wrong with my formulas and what are the correct formulas?