I need to add SUBTOTAL into this formula so I can have it run when I filter the table:
=MAX(FREQUENCY(IF(Table135[Result 1]="ITM",ROW(Table135[Result 1])),IF(Table135[Result 1]<>"ITM",ROW(Table135[Result 1]))))
It searches a column that contains ITM or OUT and looks for the highest number of consecutive occurrence of ITM. However in its current form It does not reflect when you apply filter to the table which is not ideal. But I just cannot figure our where to put in the SUBTOTAL formulas to get it to function.
I have 8 years experience on MIS, currently running training institute on MIS would be happy to help you to the requirement.
let me know in case of any further information require.
Thanks,
Srinivasu.