Array formula counting empty cells as zero

Hi. I have this array formula

=MAX(FREQUENCY(IF(ALL!$I$2:$I$5375="League",IF(ALL!$E$2:$E$5375="H",IF(ALL!$L$2:$L$5375=0,ROW(ALL!$L$2:$L$5375)))),IF(ALL!$I$2:$I$5375="League",IF(ALL!$E$2:$E$5375="H",IF(ALL!$L$2:$L$5375>0,ROW(ALL!$L$2:$L$5375))))))

which works fine up to a point. It's looking for the longest sequence of zero's. However the formula is counting blank cells as zero's so as there is more data to be added each week and there are more rows left to be filled then the formula is counting these empty cells as zero's which is some cases produces a false result. To stop this happening I have to limit the range of the formula to the last row where data has been entered. Is there a way round this or does it mean every week I have to update the array formula? Many thanks.

Probably the same issue as before. Can you post a sheet, and indicate where the problem is to be found. It's very hard to visualise a formula like that!!

Please try

=MAX(FREQUENCY(IF(ALL!$I$2:$I$5400="League",IF(ALL!$L$2:$L$5400<>"",IF(ALL!$L$2:$L$5400=0,ROW(ALL!$L$2:$L$5400)))),(IF(ALL!$I$2:$I$5400="League",IF(ALL!$L$2:$L$5400>0,ROW(ALL!$L$2:$L$5400))))))

I would be tempted to go for Named ranges at this point. These adjust automatically to suit the length of your data.

For the competion type (Comp) I used:
ALL!$I$2:INDEX(ALL!$I$2:$I$20000,SUMPRODUCT(--(LEN(ALL!$J$2:$J$20000)>0)))

the red bit ensures that it only goes down the competition column to the last result.

For the result (WDL), I used:
=ALL!$J$2:INDEX(ALL!$J$2:$J$20000,SUMPRODUCT(--(LEN(ALL!$J$2:$J$20000)>0)))

for scores home and away, set up named ranges based on WDL, as you don't want them to look at rows where no result is in.

The formula (C120) then becomes:
=MAX(FREQUENCY(IF(Comp="League",IF(WDL<>"W",ROW(WDL))),(IF(Comp="League",IF(WDL="W",ROW(WDL))))))

I am leaving now. Can you add the other named ranges? If you have issues, I'll be back tomorrow. Incidentally, you have a fair number of dead Named Ranges (CTRL-F3). I suggest you kill the ones that are giving errors.

Adapt the formula for your new range in a cell. Copy.

CTRL-F3.

New

Paste

Done

check it (sometimes they get confused and slip to whole row/column references when copy pasting, but usually only if there are non-$'d ranges, which you won't have).

Run.

The reason is simple. I didn't think of it.

So, I've got up, all ready to spend hours on this to get it right and now I'm totally confused?

The 5368 result in B112 in the example by BMV is how many rows there are?

The total amount of games played is 5367.

What am I looking at?

On the ALL sheet, in cell R5350 will always be the total amount of games played. Wouldn't using this cell in the formula make things easier? If I need it to result in 5368 I could always +1 it?

I would remove the solved tag from this thread, if I were you.

Oh, I see - I thought you needed some more help.

Don't worry - nobody will let you hog them!!!