Equation Question
Hi Smartsheet Team,
I am trying to develop a count for 2 different columns.
- Days since received date until completion
- Days until and since due date until completion
Equations:
- =COUNTIFS([Resolution Status]:[Resolution Status], <> "Resolved", [Received Date]:[Received Date], ISDATE(@cell ), [Received Date]:[Received Date], "<=" & TODAY())
- =COUNTIFS([Resolution Status]:[Resolution Status], "In Progress", [Resolution Status]:[Resolution Status], "High Priority", [Received Date]:[Received Date], ">=" + TODAY(), [Completion Date]:[Completion Date], "<=" + TODAY()
Results:
- #inparseable
- output of 0
I am using a multiple dropdown for Resolution Status column with "Resolved", "In Progress", "High Priority", "Low Priority"
Received, Due and Completion Date boxes are all dates.
What am I missing? TIA
Answers
-
Try this for #1:
=COUNTIFS([Resolution Status]:[Resolution Status], "<>Resolved", [Received Date]:[Received Date], ISDATE(@cell), [Received Date]:[Received Date], "<=" & TODAY())
Looks like the issue is with the multiselect dropdown for "Resolution Status" column.
Try this for #2:
=COUNTIFS([Resolution Status]:[Resolution Status], "In Progress", [Resolution Status]:[Resolution Status], "High Priority", [Received Date]:[Received Date], ">=" & TODAY(), [Completion Date]:[Completion Date], "<=" & TODAY())
However, since both "In Progress" and "High Priority" are in the same multi-select dropdown column, this formula won't work correctly. Smartsheet doesn't natively support searching within multi-select dropdowns using COUNTIFS. You would need to use a helper column to identify rows that contain both "In Progress" and "High Priority," then count those rows.
Helper Column Solution
Helper Column 1: Check for "In Progress":
=IF(CONTAINS("In Progress", [Resolution Status]@row), 1, 0)Helper Column 2: Check for "High Priority":
=IF(CONTAINS("High Priority", [Resolution Status]@row), 1, 0)Helper Column 3: Combine both checks:
=IF([Helper Column 1]@row * [Helper Column 2]@row = 1, 1, 0)Final COUNTIFS formula:
=COUNTIFS([Helper Column 3]:[Helper Column 3], 1, [Received Date]:[Received Date], ">=" & TODAY(), [Completion Date]:[Completion Date], "<=" & TODAY())Good Luck!
-
@ShankyPaul - unfortunately this is only producing an output which counts the resolution statuses. Im fairly new to smartsheet and do not know how to fix this issue. Any assistance is helpful.
-
You are using Excel based syntax. Smartsheet uses a different syntax.
Where exactly are you putting this formula?
-
@PaulNewcome - the #unparseable's are me trying to fix the equation based of my limited knowledge. But the boxes that list 16 and 0 are the outputs for the equations that Shanky recommended. I cannot seem to link the dates box output when referencing it on a separate sheet. So everything is on the same sheet.
-
Are you trying to count the number of days on a row by row basis?
-
For Days since received - I am trying to create a count that counts the amount of days since the line was made until the condition is switched to resolved.
For Days until due date - create a count that outputs the days until the due date from todays date. That can be up to the date or passed the date (reflects negative). This count should stop when condition is switched to resolved.
Conditions are: Resolved, High Priority and In Progress
-
Ok. That's going o be a bit different than your current formulas. Your current formulas are trying to count rows looking through entire columns. What you need is a basic date comparison.
=IF([Completion Date]@row <> "", [Completion Date]@row - [Received Date]@row)
You would use the same logic for the due date calculation. If the completion date is blank, then subtract TODAY() from the due date.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!