COUNTIFS Using a Date Column
Hi,
I have a sheet from where I simply need to count Total tasks, Total tasks not complete in date based on data in other columns including a date column. The date column is compared to a date in a cell in the Metric sheet; and should return a value based on whether the date in the column row is within or outside of the date in the Metric sheet.
I can get it to work in one formula that is counting all tasks not exceeded a due date, based on the "Report End Date", as below.
=COUNTIFS({IMPORT SHEET Range 1}, =$[Primary Column]@row, {IMPORT SHEET Range 2}, ="PPM", {IMPORT SHEET Range 7}, <=[Report End Date]@row, {IMPORT SHEET Range 5}, <>"CANCELLED")
The formula below is essentially doing the same as above, but just looking for open tasks (not completed) but the due date is after the date in the Metric sheet cell "Report End Date".
=COUNTIFS({IMPORT SHEET Range 1}, =$[Primary Column]@row, {IMPORT SHEET Range 2}, ="PPM", {IMPORT SHEET Range 6}, ISBLANK(@cell), {IMPORT SHEET Range 7}, >=[Report End Date]@row)
This formula is not working correctly and is counting all open tasks and appearing not to be limited by the "Report End Date". IMPORT SHEET Range 7 is the date column in the sheet being counted.
Unfortunately I can't share the sheet.
Any thoughts would be appreciated.
Thanks
Richard
Best Answer
-
Hello Richard,
If sharing the sheet is not possible, could you make a copy of both sheets and share only limited rows.
I am sure it will help getting a better understanding what data you have and what you want to achieve.
Grtz, Maaik
Answers
-
Hello Richard,
If sharing the sheet is not possible, could you make a copy of both sheets and share only limited rows.
I am sure it will help getting a better understanding what data you have and what you want to achieve.
Grtz, Maaik
-
Hi Maaik,
I'm not sure how or why, the formula has started to work. All I did was delete an equals sign and put it back.
Regards
Richard
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!