Sumifs, Constains, and Dates

Felicia Levy

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)))

Genevieve P.

Hi @Felicia Levy

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

Genevieve P.

Hi @Felicia Levy

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

Felicia Levy

The error is #Invalid Operation.

Time to Close is a calculated field

Thank you for your help

Felicia Levy

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

Genevieve P.

Hi @Felicia Levy

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

Felicia Levy

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!!!!!

Genevieve P.

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