Hello, I have a sheet set up to track of the number of event reports received on team members (mock example below - my real sheet has goes up to Event10).
The dates are entered manually but every time I enter the date, I want to calculate the number of event dates that fall within 3 years of the latest date I just entered. I was using this formula:
=COUNTIF([Event1]@row :[Event10]@row , >TODAY(-365 * 3))
However I noticed it was not calculating some of the dates, and I wonder if it's because I don't necessarily want it from "today's" date, but whatever the most recent date is that I enter.
I asked Smartsheet AI for a formula and it gave me this:
=COUNTIF([Event1]@row :[Event10]@row ,AND(ISDATE(@cell ), MAX([Event1]@row :[Event10]@row ) - @cell <= 1095))
When I tried this, it came up: #INVALID OPERATION
Could someone suggest another formula I could use? Many thanks in advance.
