Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

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

Answers

  • Overachievers Alumni

    Without getting too deep into the formula, a quick difference I notice is the last compare statement you wrote. If you wrote <TODAY(-45) it will not include anything entered on the day the report is run (TODAY()). So that could cause a small inconsistency between the report you wrote and the COUNT formula.

  • Thank you for highlighting that. I did add a TODAY count to the formula

  • Overachievers Alumni

    Great. Are you still having the same issue?

    If so, can you describe what you're seeing? Is it returning the wrong counts, or no value, or is it producing an error code?

  • It is returning a 0 value. I have built a report that obtains this same information via filters and grouping that has numerical values (not 0). I have been using it as my validation source

  • Overachievers Alumni

    I don't immediately see any issues with your formula as it is written. Could you provide screenshots with both sheets showing where the references are made?

  • This is the original formula with the date count formula removed from the "Initial" column:

    This is with the date count:

    The source sheet & date range:


  • Overachievers Alumni

    I think I see what may be happening. COUNTIFS has an inherent AND between all of the parameters. So you can read it out as:

    IF parameter1 is true AND parameter 2 is true AND ... etc

    In your formula, when you added TODAY(), you have now made an impossibility because a date can't be both TODAY and < TODAY(-45). I think to fix this you could remove the TODAY() function and simply replace the < with <= TODAY(-45)

  • I appreciate you helping with this. I did make the adjustment you mentioned, and I am still not returning the correct results.


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

  • AHA! This is almost comical as I have been working on this for a while... Thank you for working through this with me, I appreciate the support.

  • Overachievers Alumni

    No problem. Glad it's working

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions