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?

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

• Thanks. Had to modify slightly, as I had some non-dates in row 6 (NA for example), which affected the Later calculation, but I just ended up making Later the total number of cells with a date in them, minus the Between and Earlier values. Worked great. I hadn't used SUMPRODUCT this way before Oct 21, 2019 at 21:32

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.

• Thanks for the suggestion. Haven't tried it yet, as the non-array formula I marked as the solution did the trick, and I'd always prefer to use non-array formulas where I can for speed and futureproofing! Oct 21, 2019 at 21:33
• @bpmccain,, nothing wrongn using an array formula and above all it's the need of the situation,, once try it,, also, even for huge data set array formula works nicely,, if you have good machine ☺ Oct 22, 2019 at 7:18
• Agree for personal sheets. This one will be shared and accessible to many people who don’t understand array formulas. Too much ongoing maintenance. Thanks for the suggestion. Oct 23, 2019 at 1:18
• @bpmccain,, it's your choice,, ☺ Oct 23, 2019 at 6:37