+ Reply to Thread
Results 1 to 2 of 2

SUM, IF, FREQUENCY formula count instances of sick time off [shorter formula?]

  1. #1
    Registered User
    Join Date
    12-15-2019
    Location
    Cumbria
    MS-Off Ver
    365
    Posts
    11

    SUM, IF, FREQUENCY formula count instances of sick time off [shorter formula?]

    Hello I'm just wondering if there is a more way of my formula in column NH4

    I've repeated the formula to get the correct results but it's massive.

    I don't know how to condense the formula down.

    The formula is used to count instances of sick time off of based on the text "s" and "sh" the "rd" bridges the gap so the sickness is a single occurrence as opposed to multiple instances once you factor in rest days.


    =SUM(IF(FREQUENCY(IF(B4:NF4="s",COLUMN(B4:NF4)),IF(B4:NF4<>"s",COLUMN(B4:NF4))),1))+SUM(IF(FREQUENCY(IF(B4:NF4="sh",COLUMN(B4:NF4)),IF(B4:NF4<>"sh",COLUMN(B4:NF4))),1))-SUM(IF(FREQUENCY(IF(B4:NF4="rd",COLUMN(B4:NF4)),IF(B4:NF4<>"rd",COLUMN(B4:NF4))),1))



    I hope that makes sense?

    Any help appreciated.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 02-06-2020 at 11:24 AM. Reason: Moderator updated title

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    625

    Re: SUM, IF, FREQUENCY formula count instances of sick time off [shorter formula?]

    Quote Originally Posted by paulanderson View Post
    . . . formula in . . . NH4 . . .
    The formula is used to count instances of sick time off of based on the text "s" and "sh" the "rd" bridges the gap so the sickness is a single occurrence as opposed to multiple instances once you factor in rest days. . . .
    You're using more than 300 columns, and you're worried about a few moderately long formulas?!

    Picky: looks like your formula should end at col NC rather than col NF. That matters below.

    Your formula boils down to

    =SUM(IF(FREQUENCY(..s..),1))+SUM(IF(FREQUENCY(..sh..),1))-SUM(IF(FREQUENCY(..rd..),1))

    1st, an obvious simplification:

    =SUM(IF(FREQUENCY(..s..),1),IF(FREQUENCY(..sh..),1))-SUM(IF(FREQUENCY(..rd..),1))

    2nd, a minor change if you're willing to use array formulas.

    =COUNT(1/FREQUENCY(..s..),1/FREQUENCY(..sh..))-COUNT(1/FREQUENCY(..rd..))

    That's nice, but doesn't really cut the formula down much. Gotta say, I hadn't come across FREQUENCY bin ranges with FALSE values in them before. Interesting way to find transitions from STATE to NOT STATE. Good news is that there's another way to do that AS LONG AS the cell immediately left of the leftmost cell in the range and immediately right of the rightmost cell in the range would never contain the strings you're searching for. In that case, COUNTIFS(A4:NC4,"s",B4:ND4,"<>s") would also count transitions. Further, COUNTIFS called with arrays for even numbered arguments returns arrays. If you had s in NQ11, sh in NQ12, rd in NQ13, 1 in NR11, 1 in NR12, and -1 in NR13, try

    =SUMPRODUCT(COUNTIFS(A4:NC4,$NQ$11:$NQ$13,B4:ND4,"<>"&$NQ$11:$NQ$13),$NR$11:$NR$13)
    Last edited by hrlngrv; 02-06-2020 at 05:02 PM. Reason: typos

+ 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. [SOLVED] Help with a formula for frequency
    By Abell in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-19-2019, 01:26 PM
  2. Replies: 3
    Last Post: 01-21-2016, 02:23 PM
  3. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  4. histograms - frequency and relative frequency?
    By confusedstudent in forum Excel General
    Replies: 2
    Last Post: 02-08-2006, 04:25 AM
  5. Frequency Formula
    By Rich Z in forum Excel General
    Replies: 2
    Last Post: 09-13-2005, 05:05 AM
  6. Using Frequency formula
    By jimbo in forum Excel General
    Replies: 4
    Last Post: 08-31-2005, 05:33 AM
  7. [SOLVED] Frequency formula
    By Curious in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2005, 05:06 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