# Max consecutive positive integers with criteria

• Hi, I'm new to excel and been able to figure out most as I go but this one's stumping me..

I need to calculate max number of consecutive positive integers in H:H that also has "long" written in the corresponding E:E.

This is where I'm at currently, but I know it's not right as it's returning #VALUE, or #N/A when I turn it into an array with CTRL SHIFT ENTER

It needs to continue counting until it reaches an E:E that still says "long" but a negative or even integer in H:H, and skip over any rows where E:E isn't "long"

=MAX(FREQUENCY(IFS(E:E="long",H:H, H:H>0,H:H),IF(H:H<=0,H:H)))

• Hi,

Without a tiny sample worksheet ... difficult to design your exact solution ...

So ...did a small 11 row sheet on my own ...

You can test following

Code
``=MAX(FREQUENCY(IF(VALUE(SUBSTITUTE(H2:H12, " long",""))>0,ROW(H2:H12)),IF(VALUE(SUBSTITUTE(H2:H12, " long",""))<0,ROW(H2:H12))))``

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim thankyou for your reply. That comes up as #VALUE when I try to input it. Here I have included the worksheet I'm working through filling out. all the numbers on the left of "sheet 4" are random and not important, just being used to help fill out the equations in the right side of "sheet 4".

At the moment I'm working on filling P18 with the topic mentioned above. Once I have that I can modify it slightly to then give me equations for O18-Z18 and O19-Z19.

In essence I'm trying to test how many consecutive positive results I get when "long" (as well as how many consecutive positive results i get on "short" - but as mentioned it should be pretty easy to extrapolate the equation once I have a working one) Exluding those in the "all" columns, which are a lot easier to do as they will not include the E:E criteria

## Files

• Hello,

Huge advantage to visualize things ...!!!

Code
``=MAX(FREQUENCY(IF(H2:H32>0,IF(E2:E32="long",ROW(H2:H32))),IF(H2:H32<0,IF(E2:E32="long",ROW(H2:H32)))))``

Hope this will help

## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• I can't thank you enough Carim, I'm in you debt. Thankyou

• I can't thank you enough Carim, I'm in you debt. Thankyou

You are welcome ..!!! :smile:

Thanks a lot for your Thanks ...AND for the Like ...:wink:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!