count the number of tasks due within 30 days
Answers
-
Paul, I'm trying to figure out a basic formula to count the number of tasks coming due within 30 days, but excluding those tasks that are already complete. The formula I'm using is:
=COUNTIFS({Due Date}, >=TODAY(30), {Actual Completion %}, <> 1)
I've also tried:
=COUNTIFS({Due Date}@row, >=TODAY(30), {Owner Completion %}@row, <>1) --> returns #UNPARSEABLE
I'm using the Due Date column and Actual Completion % column (or at least I'm trying to) from my grid plan, to drive my count results in my metrics resource sheet
1: Plan
2: Metrics sheet
My guess is that I'm using the wrong bracket type in the formula...{ versus [, or it's the wrong formula altogether. Is there a better or more efficient (stable) formula to use based on the columns I have set up?
Thanks for the help!
-
Hello!
A couple of thoughts:
- First, I think you would want to use <=Today(30) as those are the tasks due in 30d or less. Your current formula would return tasks due in 30 days or further.
- Remove the @row from your formula. You shouldn't need this when referencing another range.
Hope this helps!
-
Yes. The first COUNTIFS should work if you swap the "greater than" out for a "less than".
-
Thank you Javed and Paul. The solution is partially working. It looks like the issue is with this: {Actual Completion %}, <>1
I'm using this formula below, with the due date column selected as my Range 1. If I remove {Actual Completion %}, <> 1, then I get a return. But adding it back in gives me #INVALID REF. The data format in the {Actual Completion %} column cells is in "%" format. Is there a chance the formula doesn't like %?
=COUNTIFS({Due Date}, >=TODAY(30), {Actual Completion %}, <> 1)
Thank you!
-
Correction: I changed the greater than, to less than already. Then had the issue still persist as described above. Here's my current formula:
=COUNTIFS({Due Date}, <=TODAY(30), {Actual Completion %}, <> 1)
Also, I'm referencing another column in another sheet, if that additional info helps.
Thank you!
Brad
-
HOw is the data in the other sheet being populated?
-
I am manually entering the finish dates when tasks are completed, then the actual percentage is auto calculated from the owner percentage (which is manually entered).
Example below:
Complete date = manually entered
Owner Completion % = manually entered
Actual Completion % = auto calculated from "Owner Completion %" field
The fields I'm referring to in my formula is looking at the "Due Date" field (manually entered), and the "Actual Completion %" field.
Hope this helps!
-
Is the Due Date column set as an actual date type column? What is the formula in the Actual % Complete Column? When you enter data into the Owner % Complete column, are you having to manually input the % sign or are you able to just enter a number and the % sign is automatically added to it?
-
- Is the Due Date column set as an actual date type column? YES
- What is the formula in the Actual % Complete Column?
- =IF([In Scope?]@row = 0, "", IF([Start Date]@row = "", "", IF($C@row > 0, AVG(CHILDREN()), IF([Owner Completion %]@row = "", 0, [Owner Completion %]@row))))
- When you enter data into the Owner % Complete column, are you having to manually input the % sign or are you able to just enter a number and the % sign is automatically added to it?
- the % sign is automatically added to it
-
Would it make a difference if I switched to the thousands separator, to overcome any system issues with the "%" sign?
-
Lets run some tests on the source data...
Insert a temporary text/number column next to both % Completes and use
=IF(ISNUMBER([Owner Completion %]@row), "", "TEXT")
Do the same for the other % Complete. Are there any rows that contain "TEXT" in the temp columns?
Same logic for the date. Insert a temporary text/number column and use
=IF(ISDATE([Due Date]@row), "", "TEXT")
Any rows with "TEXT" in them?
-
No rows with "TEXT" in any of the 3 columns. I did notice that the if I removed the value in the cell, then it would return a "TEXT" value in the corresponding TEMP column.
-
Removing the value and getting "TEXT" in the temp column is to be expected.
I just now saw the error you posted before. Invalid Reference means the cross sheet reference was not set up properly. Ensure that you have those both set up properly (clicking on the appropriate column header and whatnot) and see if that clears it up. I was working based on the (obviously incorrect) thought that you were just getting a zero as a result.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!