1

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?

enter image description here

0

2 Answers 2

2

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))

enter image description here

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

1
  • 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
    – bpmccain
    Oct 21, 2019 at 21:32
0

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.

enter image description here

  • 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.

4
  • 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!
    – bpmccain
    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.
    – bpmccain
    Oct 23, 2019 at 1:18
  • @bpmccain,, it's your choice,, ☺ Oct 23, 2019 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.