FORMULA
FORMULA TO COUNT IF ENGINEERING REQUEST DATE IS 3 DAYS LATER THAN TODAY OR IF PURCHASING REQUEST DATE IS BLANK
Best Answer
-
Found a couple mistakes. Try:
=IF(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=today(), ISBLANK([purchasing request date]@row)), 1, IF(OR(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=TODAY()), ISBLANK([purchasing request date]@row)), 0, ""))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi,
Try:
=COUNTIF([engineering request date]:[engineering request date], @cell =TODAY(3))+ COUNTIFS([purchasing request date]:[purchasing request date], ISBLANK(@cell), requester:requester, IS TEXT(@cell))-COUNTIFS([purchasing request date]:[purchasing request date], ISBLANK(@cell), requester:requester, IS TEXT(@cell), [engineering request date]:[engineering request date], @cell =TODAY(3))
A bit long but it counts engineer dates and adds blank purchase request dates, then subtracts cases where both conditions are true to eliminate double counting.
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
Thanks for the info, but I was unable to get this to work. I am looking to get a 1 in the formula column if these condition are met. Engineering request date is past 3 days and purchasing request date is blank, a 0 in formula column if neither condition is met.
Thanks,
-
Hi John, Thanks for clarifying. The formula to do that is:
=IF(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=today(), ISBLANK(([purchasing request date]@row), 1, IF(OR(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=TODAY()), ISBLANK(([purchasing request date]@row), 0, ""))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
It did not, got incorrect Argument set.
Thanks,
-
Found a couple mistakes. Try:
=IF(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=today(), ISBLANK([purchasing request date]@row)), 1, IF(OR(AND([engineering request date]@row>=TODAY(-3), [engineering request date]@row<=TODAY()), ISBLANK([purchasing request date]@row)), 0, ""))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!