Countifs Date range if/then question
I am trying to count the instances that the final approvals were NOT late. The late flag pops if Final is past Target. The issue I am running into is that I am getting a false result due to the blanks where the final approval has not been given. Is there a way to count the blanks "" in the Final Approval Late? column but take not count them if the Final Approval On date is blank as well?
I thought this would work as a starting point, but I get zero as an answer for everything and that is not correct.
=COUNTIFS({RBI Deviations Tracker Range 1}, [Primary Column]@row, {RBI Deviations Tracker Range 2}, [Column 1]@row, {RBI Deviations Tracker Range 5}, <={RBI Deviations Tracker Range 6})
Thank you!
Answers
-
@Ryon You can probably do it in 2 steps. Use "Final Approval Late?" column to denote if the approval is late or not. In that column use an if formula with below conditions:
If "Final Approval On" is blank then
check if Target date is already passed (less than today()) then
mark "late"
else
leave blank
else
if "Final Approval On" date is less than target date then
leave blank (not late)
else
mark "late"
Now count instances of "late" on "Final Approval Late?" column to identify late approval instances.
Regards,
Saurabh Maheshwari
Smartsheet Superstar
Portfolio Lead, TCS
-
Thank you, that worked once I had a bit more explanation from a teammate. It seems that I have a lot more to learn than I thought haha!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 303 Events
- 34 Webinars
- 7.3K Forum Archives