# Dertiming Maximum count of "1" in a frequency across rows

1. ## Dertiming Maximum count of "1" in a frequency across rows

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))))  Register To Reply

2. ## Re: Dertiming Maximum count of "1" in a frequency across rows

i suspect i'm missing something
would you just do a countif()

=countif(H9:NH9, 1)  Register To Reply

3. ## Re: Dertiming Maximum count of "1" in a frequency across rows

Yes - it's not that simple. He could work from Jan 1st to 23rd then be off for 5 days. Then work for 20 days and off for 12 and then work for 25 days. I only want to see the maximum count of one continuous work stint. So the 25 days would be what I want returned as the answer to the formula. This formula worked for me when it was either a "1" or a blank cell, but now that there is text in some cells - it seems to be counting the text.  Register To Reply

4. ## Re: Dertiming Maximum count of "1" in a frequency across rows

=MAX(FREQUENCY(IF(H9:NH9 = 1, COLUMN(H9:NH9)), IF(H9:NH9 <> 1, COLUMN(H9:NH9))))

The formula MUST be confirmed with Ctrl+Shift+Enter.  Register To Reply

5. ## Re: Dertiming Maximum count of "1" in a frequency across rows

Perfect - that worked. Thank you!!  Register To Reply

6. ## Re: Dertiming Maximum count of "1" in a frequency across rows

You're welcome.  Register To Reply