IF(COUNTIFS......Help Request
Main Sheet:
My Current Data Sheet:
*I already have a Formula on the Data Sheet for "Client" to look at the Main Sheet and if a Client Name is the same, return the value once. And for "Deliverable" formula to return the count value of a deliverable if "Status" not equal to 'Waiting on Client'
Now I want to add an additional Column to my Data Sheet called "Late". I am trying to have the "Late" column to provide me the count of each late deliverable if the "Deliverable" is not blank and Status is not equal to 'Waiting on Client' with the "Due Date" is less than Today.
I tried the Formula below and I know it is missing AND "Status" is not equal to 'Waiting on Client'
=IF(COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"") = 0, "", COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>""))
The End Result should look like this:
Thank you advance on any suggestion.
Best Answer
-
=IF(COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"",{Status},<>"Waiting on Client") = 0, "", COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"",{Status},<>"Waiting on Client"))
This should works. Have you tried it?
Answers
-
=IF(COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"",{Status},<>"Waiting on Client") = 0, "", COUNTIFS({Client}, Client@row, {Due Date}, <TODAY(), {Deliverable}, <>"",{Status},<>"Waiting on Client"))
This should works. Have you tried it?
-
Thank you. This worked flawlessly. I spent so much time trying to figure this out. And voila!
-
What if I want to change the requirement from past date to due within a week?
I tried multiple approaches, and I am unable to get to work accurately.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!