# Equation Question

edited 08/27/24

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:

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

• ✭✭✭✭✭
edited 08/27/24

Try this for #1:

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!