Sum Scripts added with the past week formula

Kerri S

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.

Genevieve P.

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

Genevieve P.

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

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.

Glad you were able to get this working! 🙂