Equation Question

CC_ADSK

Hi Smartsheet Team,

I am trying to develop a count for 2 different columns.

1. Days since received date until completion
2. Days until and since due date until completion

Equations:

1. =COUNTIFS([Resolution Status]:[Resolution Status], <> "Resolved", [Received Date]:[Received Date], ISDATE(@cell ), [Received Date]:[Received Date], "<=" & TODAY())
2. =COUNTIFS([Resolution Status]:[Resolution Status], "In Progress", [Resolution Status]:[Resolution Status], "High Priority", [Received Date]:[Received Date], ">=" + TODAY(), [Completion Date]:[Completion Date], "<=" + TODAY()

Results:

1. #inparseable
2. 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

Shanky Paul

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!

CC_ADSK

@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.

Paul Newcome

You are using Excel based syntax. Smartsheet uses a different syntax.

Where exactly are you putting this formula?

CC_ADSK

@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.

Paul Newcome

Are you trying to count the number of days on a row by row basis?

CC_ADSK

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

Paul Newcome

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.