Sum if 5 consecutive numbers are> 1

[Berna11]

Good morning I would need a formula to check if there are 5 consecutive numbers >1 and add them.

In case there were 2 sequences of 5 numbers > 1 add the one with the highest value.

In the case of column E the consecutive numbers >1 are 7 and we must see which combination gives the largest value as in the example F16: H20

The result entered by hand is in G2: J2
Thank you

[XLent]

One possible option:

Please Login or Register  to view this content.

Note: above will generate 8.8 for H2, unclear why this is 0 in your example - can you clarify?

{per locale settings you may need to translate above into Italian equivalents}

[Berna11]

Thank you XLent
it is almost perfect there is to rectify that value of 8.8 in H2.
All 5 numbers must be> 1 to make the sum otherwise it remains 0.
I hope it is clear.
Thanks again.

[XLent]

Ah, apologies, change the COUNTIF criteria to "<1" (currently 0)

In essence the calculation creates multiple ranges of 5x1 from first cell onwards, the ranges are aggregated with resulting number multiplied by 0/1 depending on whether, or not, the COUNTIF check verifies that all values meet criteria; the outer MAX then just pulls the highest value should there be multiple (valid) aggregates.

EDIT: apologies, should be "<=1" .. I am multi-tasking, poorly!

[Berna11]

Ok for H2 <1.

But another problem has arisen.
See attached column B
if you increase the range and there are 3 consecutive numbers> 1 and the total is greater than the previous one, take these 3 numbers from B30: B32.

The correct one is B2: B6 5 num> 1.

[XLent]

Hi, yes, you will not in earlier example the OFFSET was limited to rows B2:B21, i.e. last 4 rows ignored as we don't want to create 5 row arrays anchored on those cells as they are incomplete, so either:

a) modify the formula you adjusted in G2 to reference B$2:B$28 rather than B$2:B$32 (the last anchor for the OFFSET (B28) will generate the final 5 cell range B28:B32)

Please Login or Register  to view this content.

or

b) modify the COUNTIF test such that instead of verifying that no cells are <1 you can test that COUNT of those included that are >=1 =5)

Please Login or Register  to view this content.

use whichever makes most sense... no significant difference in approach.

[Berna11]

Now it seems perfect
thank you so much
see you next time

[Berna11]

I'm trying to improve the formula but without success.
in practice I would like to check the month between column A and column F
In G1 I did a test but as you can see, the total does not come back ...
You know how I can change
Thanks again

P.S
in the case the 5 consecutive numbers overlap two months are not to be considered.
Only if consecutive in a month.
Already that we are also insert year A = year F

[XLent]

If, per example, every day in a given calendar month is listed in Column A you could use:

Please Login or Register  to view this content.

In terms of your Work In Progress calc (G1) I would always advise against the use of OFFSET in conjunction with SUMPRODUCT unless absolutely necessary

[Berna11]

I apologize
the first cell works the others from # N / A error
maybe mistake translation formula

P.s.
my mistake had taken off the $

....MATCH($F2,A:$A,0)),....

Solved