Sum Scripts added with the past week formula

I need help identifying a formula. When the Timing, Affected Environment(s) and Script Type match, identify the Execution Date within the past 7 days and calculate the difference between Total Executed to update the Added in last week. In the example below, this formula circled in red should return '0'. Since Timing, Affected Environment(s) and Script Type match, and their is an Execution Date in the prior 7 days, the difference between the Total Executed of those rows is '0'.

I know I need to start with 'SUMIF' but I'm confused how to thread in the prior 7 day date logic. Thanks for the support.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Kerri S

    If I'm understanding you correctly, you'd like to count how many rows in this sheet have the same values in 3 cells and were submitted within the last week. Then you'd like to subtract that number from the "Total Executed" number to the right.

    In a COUNTIFS Function, you first list the Column you're looking into and then the value you're searching for. First, lets list the 3 matching values:

    =COUNTIFS(Timing:Timing, Timing@row, [Affected Environment(s)]: [Affected Environment(s)], [Affected Environment(s)]@row, [Script Type]:[Script Type], [Script Type]@row

    Then we can add in the Date timeframe, if the date is within the last 7 days... or greater than 7 days ago but less than or equal to Today's date (see: TODAY Function)

    =COUNTIFS(Timing:Timing, Timing@row, [Affected Environment(s)]: [Affected Environment(s)], [Affected Environment(s)]@row, [Script Type]:[Script Type], [Script Type]@row, [Execution Date]:[Execution Date], [Execution Date]@row > TODAY(-7), [Execution Date]:[Execution Date], [Execution Date]@row <= TODAY())

    Once you have this COUNT, you can subtract it from the cell on the right:

    =[Total Executed]@row - COUNTIFS(Timing:Timing, Timing@row, [Affected Environment(s)]: [Affected Environment(s)], [Affected Environment(s)]@row, [Script Type]:[Script Type], [Script Type]@row, [Execution Date]:[Execution Date], [Execution Date]@row > TODAY(-7), [Execution Date]:[Execution Date], [Execution Date]@row <= TODAY())


    Let me know if this works for you and it's what you were looking to calculate!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Kerri S

    If I'm understanding you correctly, you'd like to count how many rows in this sheet have the same values in 3 cells and were submitted within the last week. Then you'd like to subtract that number from the "Total Executed" number to the right.

    In a COUNTIFS Function, you first list the Column you're looking into and then the value you're searching for. First, lets list the 3 matching values:

    =COUNTIFS(Timing:Timing, Timing@row, [Affected Environment(s)]: [Affected Environment(s)], [Affected Environment(s)]@row, [Script Type]:[Script Type], [Script Type]@row

    Then we can add in the Date timeframe, if the date is within the last 7 days... or greater than 7 days ago but less than or equal to Today's date (see: TODAY Function)

    =COUNTIFS(Timing:Timing, Timing@row, [Affected Environment(s)]: [Affected Environment(s)], [Affected Environment(s)]@row, [Script Type]:[Script Type], [Script Type]@row, [Execution Date]:[Execution Date], [Execution Date]@row > TODAY(-7), [Execution Date]:[Execution Date], [Execution Date]@row <= TODAY())

    Once you have this COUNT, you can subtract it from the cell on the right:

    =[Total Executed]@row - COUNTIFS(Timing:Timing, Timing@row, [Affected Environment(s)]: [Affected Environment(s)], [Affected Environment(s)]@row, [Script Type]:[Script Type], [Script Type]@row, [Execution Date]:[Execution Date], [Execution Date]@row > TODAY(-7), [Execution Date]:[Execution Date], [Execution Date]@row <= TODAY())


    Let me know if this works for you and it's what you were looking to calculate!

    Cheers,

    Genevieve

  • Kerri S
    Kerri S ✭✭

    Since my post, we altered the date lookback. Now they want to look at the same rows that match and get the Max Date instead of the last 7 days. We added a Max(Collect) function in a separate column, then used that field as we got the 'Nested Criteria' error when the Max(Collect) was embedded. Thanks so much!

  • Genevieve P.
    Genevieve P. Employee Admin

    Glad you were able to get this working! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!