Countif Date Array Formula

I'm trying to build a sheet for tracking compliance with training. I'm needing to build some summary fields, which show how many procedures a person is current on, how many they are out of date with, and how many have had a new procedure issued since they were trained.

So for the data shown below, I need:

• one calculation that looks at the three dates next to person A, and figures out how many of them are later than both Date 1 and Date 2 above them
• one calculation that looks at the three dates next to person A, and figures out how many of them are in between Date 1 and Date 2 above them
• one calculation that looks at the three dates next to person A, and figures out how many of them are earlier than both Date 1 and Date 2 above them

I've been trying an array formula using the below, but it doesn't seem to work as I hoped. It only works if the date next to person A is bigger than every single date next to Date 1 and Date 2, not just in the column above it.

=COUNTIFS($G65:$GX65,">="&$G$10:$GX$10,$G65:$GX65,">="&$G$6:$GX$6)

I've also tried adding a character to the date next to person A using conditional formatting, but the COUNTIF function doesn't find that character if added by formatting.

Any tips?

2 Answers

If it is the case that, as in your example, Date 1 is always later than Date 2, you can use these formulas:

 Later: =SUMPRODUCT(--($E6:$G6>$E$2:$G$2))
Between: =SUMPRODUCT(($E6:$G6<=$E$2:$G$2)*($E$6:$G$6>=$E$3:$G$3))
Earlier: =SUMPRODUCT(--($E$6:$G$6<$E$3:$G$3))

If Date 1 is NOT always the later date, the formulas would just need to be expanded a bit to be comparing the proper date

Since OP mentioned to calculate, three dates next to Person A, and figures out how many of them are later, between and earlier than both Date 1 and Date 2, above them

Therefore, I've used COUNTIF & COUNTIFS to compare dates in G69:I69 with date 1 & 2 in G65:I66.

• Array (CSE) formula in Cell D69:

  {=COUNTIF($G$69:$I$69,">"&$G$65:$I$66)}

Dates are 01-Jan-20 & 15-Oct-19.

• Array (CSE) formula in Cell E69:

  {=COUNTIF($G$69:$I$69,"<"&$G$65:$I$66)}

Dates is 01-Sep-19.

• Array (CSE) formula in Cell F69:

  {=COUNTIFS($G$69:$I$69,">="&MIN($G$65:$I$66),$G$69:$I$69,"<="&MAX($G$65:$I$66))}

Dates are 01-sep-19 & 15-Oct-19.

N.B.

• Finish formula in D69, E69 & F69 with Ctrl+Shift+Enter.

• Adjust cell references in formula as needed.