CountIFs Formula including specific values within a date range.

I am trying to write a formula count the number of file completions per team member of each specific file type within 45 days. These values are being pulled from another sheet. This is what I have so far, but it is not matching a simple report that I built with a 45 day filter. For charting purposes, I need this formula.

NOTE: the reference sheet is the same but for some reason the title is different in the first two reference ranges

=COUNTIFS({Focus Tracker Range 1}, [Assigned to]@row, {Focus Tracker Range 2}, "Initial", {Completed Files Focus Tracker Range 1}, <TODAY(-45))

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    I'm sorry about all the back and forths on this one, but I just noticed something in the formula that I've been glossing over as we've been trying to work through the issue.

    When you say <= TODAY(-45), I think what that is doing is taking today (2/22/22) and subtracting 45 days (01/08/2022), and then using that in a formula. If that's true, then we're asking the formula to return everything with a date that is less than or equal to 01/08/2022. That would only return dates that were prior to or equal to 01/08/2022. That's not what you want, I don't think.

    Given this information I think we want >= TODAY(-45). This would look for anything that occurred on 01/08/2022 and greater.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!