Sum based on condition

[ardrianz]

Hi
I would like to request your help in creating excel formula for the following 2 scenarios:
1. Number of consecutive months in which the value was above 3000. Also, the sum of the values of first 3 consecutive months of 3000 or above along with the month in which it was completed
2. Sum of the values each month above 50 or more - if the value falls below 50 in any month then there should be no further addition to the total.

Please find attached the workbook containing the details.

Thank you.

 

[ardrianz]

Please help

 

[Vpaul]

Not sure if a formula can help here.
May be try writing a macro - look through cell, checking values and performing your desired action.

 

[Peter Bartholomew]

I can obtain the values you quote but it is by using an Excel version you are unlikely to have access to and functions that are still only available to insider beta subscribers. Might not be seen as terribly useful!
Consecutive months:

= LET(
   threshold, 3000,
   consecMonths, SCAN(0, value, LAMBDA(consec, v, IF(v>=threshold, consec+1, 0))),
   MAX(consecMonths))

Balance from 3rd consecutive month

= LET(
   threshold, 3000,
   consecMonths, SCAN(0, value, LAMBDA(consec,v, IF(v>=threshold, consec+1, 0))),
   balance, SCAN(0, value, LAMBDA(consec,v, IF(v>=threshold, consec+v, 0))),
   XLOOKUP(3, consecMonths, balance))

3rd consecutive month:

= LET(
   threshold, 3000,
   consecMonths, SCAN(0, value, LAMBDA(consec,v, IF(v>=threshold, consec+1, 0))),
   XLOOKUP(3, consecMonths, month))

Final balance for 3rd month or following:

= LET(
   threshold, 50,
   consecMonths, SCAN(0, value, LAMBDA(consec,v, IF(v>=threshold, consec+1, 0))),
   balance, SCAN(0, value, LAMBDA(consec,v, IF(v>=threshold, consec+v, 0))),
   XLOOKUP(MAX(consecMonths), consecMonths, balance))

 

[deciog]

Peter, Good morning.

The spreadsheet does not have the posted formulas

decio

 

[Peter Bartholomew]

Peter, Good morning.

The spreadsheet does not have the posted formulas

decio

Sorry. Second go lucky?

 

[deciog]

Peter

Thank you, I liked the formulas REDUCE

decio

 

[ardrianz]

Thank you Peter. As you have mentioned, these formulas are not featuring in my MS Excel version (Office 365) so I am not able to implement it.
But thank you once again for your time and your inputs.