Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a problem with the formula arrays in the attached workbook L2:L17. The current formula array is =COUNTIF($E2:$E8,"<>0")*$K$1. However, I need L2:L17 to calculate work week hours that will exclude weekends and designated holidays from a LOOKUP table. I understand the limitations of the COUNTIF worksheet function, but there must be another way that doesn't involve converting this to a pivot table. Perhaps changing up the company holiday lookup table? I don't know. Any help would be much appreciated.

    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this in L3:

    =SUMPRODUCT(($C$2:$C$106=I3)*($F$2:$F$106>1)*($F$2 :$F$106<7)*(ISERROR($G$2:$G$106)))*$K$1

    This formula can be filled down.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That worked perfectly. Thank you.

    Amy

Posting Permissions

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