# Sumifs, Constains, and Dates

Options

I'm trying to calculate the average time it takes to close a ticket. I know that I want to use SUMIFS/COUNTIFS. The COUNTIFS formula works fine: =COUNTIFS([Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), [Start/Raised]:[Start/Raised], AND(@cell <= TODAY(), @cell > TODAY(-30)))

I'm struggling with the SUMIFS. I want to add the cells in Time to Close if the Initiative/Program Contains "Metrics/Reporting" and the Start Date is within the last 30 days.

I'd appreciate any help with my fomula.

=SUMIFS([Time to Close]:[Time to Close], [Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), [Finish/Due]:[Finish/Due], AND(@cell <= TODAY(), @cell > TODAY(-30)))

Tags:

• Employee
Options

Thank you for this information!

I tested your formula again and I can confirm that your original structure and statement is correct and will work:

=SUMIFS([Time to Close]:[Time to Close], [Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), [Finish/Due]:[Finish/Due], AND(@cell <= TODAY(), @cell > TODAY(-30)))

However if any of the columns you're referencing contain even one cell with an error, that error will then create a domino effect where it rolls up to this formula, too.

It sounds like your Time to Close column may have one cell where there's #Invalid Operation because one of the dates may be text.

Try wrapping an IFERROR around your Time to Close formula, like so:

=IFERROR(IF(Status@row = "Closed", [Finish/Due]@row - [Start/Raised]@row, TODAY() - [Start/Raised]@row), "")

This should then resolve the Summary formula, as well!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Employee
Options

The structure of your SUMIFS looks correct! Are you receiving an error or an incorrect result?

If the formula is returning 0, it may be due to how the values are appearing in your [Time to Close] column. How do you input values into that column, do you use another formula?

It would be helpful to see a screen capture with the formula open in Smartsheet, but please block out sensitive data.

Thanks!

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

The error is #Invalid Operation.

Time to Close is a calculated field

• Options

@Genevieve P. ---Posted screen shots for your review. Any help/guidance you can provide will be much appreciated. I've also tried this formula but without luck.

=SUMIFS([Time to Close]:[Time to Close], [Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), Status:Status, (@cell = "Closed"), [Finish/Due]:[Finish/Due], AND(@cell <= TODAY(), @cell > TODAY(-7)))

• Employee
Options

Thank you for this information!

I tested your formula again and I can confirm that your original structure and statement is correct and will work:

=SUMIFS([Time to Close]:[Time to Close], [Initiative/Program]:[Initiative/Program], CONTAINS("Metrics/Reporting", @cell), [Finish/Due]:[Finish/Due], AND(@cell <= TODAY(), @cell > TODAY(-30)))

However if any of the columns you're referencing contain even one cell with an error, that error will then create a domino effect where it rolls up to this formula, too.

It sounds like your Time to Close column may have one cell where there's #Invalid Operation because one of the dates may be text.

Try wrapping an IFERROR around your Time to Close formula, like so:

=IFERROR(IF(Status@row = "Closed", [Finish/Due]@row - [Start/Raised]@row, TODAY() - [Start/Raised]@row), "")

This should then resolve the Summary formula, as well!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

You are amazing! That was exactly the issue. Not a problem with the formula but an issue with the data in the column. Thank you so much!!!!!

• Employee
Options

No problem at all! That's a tricky issue to identify - I'm glad we were able to resolve it for you. 🙂