+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    06-13-2014
    Posts
    3

    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))))

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    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)
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    06-13-2014
    Posts
    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.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    06-13-2014
    Posts
    3

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

    Perfect - that worked. Thank you!!

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

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

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  4. Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  5. [SOLVED] Filtering "maximum" rows
    By Ciccio Tom in forum Excel General
    Replies: 3
    Last Post: 04-05-2006, 12:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1