formula help
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: formula help
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2012
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default formula help

    I have this formula looking at range D7:AH7 for the word sick..can the formula not count the weekends?

    Code:
    =COUNTIF($D7:$AH7,"SICK")
    Cheers

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    Where are the dates to count weekdays?

  3. #3
    Board Regular
    Join Date
    Oct 2012
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    Quote Originally Posted by FryGirl View Post
    Where are the dates to count weekdays?
    Hi

    Dates in C6:AH6

    Thanks

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    Does this work for you?

    =SUMPRODUCT(--($D$7:$AH$7="SICK"),--(WEEKDAY($D$6:$AH$6,3)<5))

    I assumed you meant D6:AH6 not C6:AH6

  5. #5
    Board Regular
    Join Date
    Oct 2012
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    Quote Originally Posted by FryGirl View Post
    Does this work for you?

    =SUMPRODUCT(--($D$7:$AH$7="SICK"),--(WEEKDAY($D$6:$AH$6,3)<5))

    I assumed you meant D6:AH6 not C6:AH6
    Yes your correct about the range

    Yes formula works great....how does the formula work please?

    What are the lines hightlighted red in your formula?

    Thanks

  6. #6
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    It's called a double unary. It is used to convert an internal array of TRUE/FALSE into 1/0

    This is a good source for further explanations.

    http://xldynamic.com/source/xld.SUMPRODUCT.html

    The Weekday part tests the weekday of the date which ends up giving you...

    {3,4,5,6,0,1,2,3,4,5,6,0,1,2,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5}<5)

    Now when you check for less than 5 (Sat and Sun), the numbers turn into TRUE/FALSE which is multiplied by the first TRUE/FALSE looking for the word "SICK".

    To get a better understanding of how and what a formula is doing, use the Evaluate Formula function

  7. #7
    Board Regular
    Join Date
    Oct 2012
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    Thank you

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Posts
    989
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    You're welcome. Happy to help!

  9. #9

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    hi .

    is this shorter ? seems to work with me: (FRI - SAT = OFFDAYS)

    @ E7
    Code:
    =SUMPRODUCT((WEEKDAY(E3:T3)<6)*(E4:T4="SICK"))

    Excel 2010
    EFGHIJKLMNOPQRST
    2FSSSMTWTHFSSSMTWTHFS
    31-Jan-162-Jan-163-Jan-164-Jan-165-Jan-166-Jan-167-Jan-168-Jan-169-Jan-1610-Jan-1611-Jan-1612-Jan-1613-Jan-1614-Jan-1615-Jan-1616-Jan-16
    4SICKSICKSICKSICKSICKSICK
    5
    6
    73

    Sheet1




  10. #10

    Join Date
    Sep 2015
    Posts
    130
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula help

    why the "--" ? can you explain why we need the numeric conversion here ?

    thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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