I am attempting to quickly determine the number of days a person works in a row. Column headings are each day of the year. Row C contains all the employees. If the employee is working on a day - then "1" is inserted into the cell that corresponds with that day. If he's off - the word "Off" will be in those cells. At the end of the spreadsheet - last column after Dec 31st , I am trying to put a formula in to count the maximum string of days worked in a row. The employee is not allowed to work past 24 so I need a flag when this happens. My formula that I'm using is counting both text and the numbers, but I only want it to count the number "1" and ignore anything else, both blank and text.
My formula is and array and is:
=MAX(FREQUENCY(IF(H9:NH9>=1,COLUMN(H9:NH9)),IF(H9:NH9<1,COLUMN(H9:NH9))))
Bookmarks