Sumifs, Constains, and Dates

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:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • The error is #Invalid Operation.

    Time to Close is a calculated field

    Thank you for your help

  • @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.
    Genevieve P. Employee Admin
    Answer ✓

    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

  • 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.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!