Within the last 12 months

Carmen Howard

I can't get my formula to pull violations reported only within the last 12 months. Can someone see what I am missing?

=COUNTIFS({Compliance Master File V2 EE-number}, [EE Number]@row, {Compliance Master File - Compliance Category}, "Reinstatement"), AND({Compliance Master File V2 Violation Date}<=[Today]@row, @{Compliance Master File V2 Violation Date}>= TODAY(-365)))

I have my historical count and I added a "today" column, if that is not needed - would love to remove it.

My working formula:

=COUNTIFS({Compliance Master File V2 EE-number}, [EE Number]@row, {Compliance Master File V2 - Compliance Category}, "Reinstatement")

jessica.smith

I simplified your cross-sheet ref names for demonstration, but does this get you the data you are looking for:

=COUNTIFS({Master_EE}, [EE Number]@row, {Master_Category}, "Reinstatement", {Master_ViolationDate}, >=TODAY(-365))

Paul Newcome

Try this instead:

=COUNTIFS({Compliance Master File V2 EE-number}, [EE Number]@row, {Compliance Master File - Compliance Category}, "Reinstatement", {Compliance Master File V2 Violation Date}, AND(@cell <= TODAY(), @cell >= TTODAY(-365)))

jessica.smith

I simplified your cross-sheet ref names for demonstration, but does this get you the data you are looking for:

=COUNTIFS({Master_EE}, [EE Number]@row, {Master_Category}, "Reinstatement", {Master_ViolationDate}, >=TODAY(-365))

Carmen Howard

That worked, thanks so much!!