Using Frequency to count numbers but skip blanks or 0's

[bramdana]

Hi all.

I am running a table at work to determine who completes their work to the best standard.

I am counting the occurrences that person hits 100% continuously.

The script below works for this:

=MAX(FREQUENCY(IF($D17:$CG17=100,COLUMN($D17:$CG17)),IF($D17:$CG17<>100,COLUMN($D17:$CG17))))

Then Crtl Shift Enter

However the issue I've found is that if someone goes on holiday or has a week off the table populates a zero or a blank and this then discounts the longest streak.

100	100	100	100	100
100		100	100	100
100	100	100	100	100

<tbody>
</tbody>

As you can see above the answer should be 5,4,5 for the longest streak of 100's However I am getting 5 3 5 because the middle guy had a holiday.

I have tried the AND statement however kept getting a value error.

=MAX(FREQUENCY(IF($D17:$CG17=100,COLUMN($D17:$CG17)),IF(AND($D17:$CG17=100,$D17:$CG17>0),COLUMN($D17:$CG17))))

Any help is appreciated please.

 

[Aladin Akyurek]

How do you get 4 for the second row?

 

[gaz_chops]

Suggest you stick with your original post - http://www.mrexcel.com/forum/excel-questions/864478-help-counting-current-streaks.html

There are a couple of solutions already posted there.

 

[Joe4]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rules # 9 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html).