SUMIFS - Sum, Restart, Sum Again

Andy_B

Hello Smartsheet Community!

I am trying to create a use a SUMIFS formula to add values in a column that are less than 20 within a 7 day range. Once they cross the threshold of 20, the formula should put a checkbox in the row that breached the threshold.

Next I want to repeat the process in successive rows, but ignore any rows used to cross the first threshold of 20.

I have a working formula for the first part, but I am uncertain how to accomplish the next phase.

Any suggestions would be helpful!

This is the working formula I have used that will yield the results in the chart below (date #s in the 100s). I am wanting an output that looks like the lower portion of the chart (date #s in the 200s).

=IF(SUMIFS(Amount:Amount, Amount:Amount, ABS(@cell ) < 20, [Date #]:[Date #], >=([Date #]@row - 6), [Date #]:[Date #], <=[Date #]@row) >= 20, 1, 0)

Gia Thinh

Hi @Andy_B

Based on your description, I have made a simulation as shown below.

>20 column :

=IF(SUMIFS(Amount:Amount, Amount:Amount, ABS(@cell ) < 20, [Date #]:[Date #], >=([Date #]@row - 6), [Date #]:[Date #], <=[Date #]@row) >= 20, 1)

>20 excluded column :

=IF(AND([>20]@row = 1, COUNT(COLLECT([>20]:[>20], [>20]:[>20], 1, [Date #]:[Date #], >=([Date #]@row - 6), [Date #]:[Date #], <=[Date #]@row)) = 1), 1)

Is this what you want?

Andy_B

Hi @Gia Thinh,

Thanks for taking a crack at this! Unfortunately, I am still hitting some snags when I tried your suggestion. I created a new data set with consecutive days and this is what the formula produced:

The column on the right is what I am trying to accomplish with a formula. The logic would run something like this:

10 + 9 + 9 = 28 Cumulative sum is 20 or more, place a check ✔️ (Ignore Date # 302 - $20 or more)

Restart counting from zero

5 + 8 + 11 = 24 Cumulative sum is 20 or more, place a check ✔️

Restart counting from zero

15 + 5 = 20 Cumulative sum is 20 or more, place a check ✔️

Restart counting from zero

6 + 19 = 25 Cumulative sum is 20 or more, place a check ✔️ (Ignore Date # 309 - $20 or more)

Restart counting from zero

Let me know if you have any further thoughts!