CountIFs Formula including specific values within a date range.

Spencor_Luze

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

David Tutwiler

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.

David Tutwiler

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.

Spencor_Luze

https://community.smartsheet.com/discussion/comment/320306#Comment_320306

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

David Tutwiler

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?

Spencor_Luze

https://community.smartsheet.com/discussion/comment/320315#Comment_320315

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

David Tutwiler

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?

Spencor_Luze

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:

David Tutwiler

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)

Spencor_Luze

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

David Tutwiler

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.

Spencor_Luze

https://community.smartsheet.com/discussion/comment/320391#Comment_320391

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.

David Tutwiler

No problem. Glad it's working