+ Reply to Thread
Results 1 to 4 of 4

Sumproduct for month to date (ONLY WORKING DAYS)!

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Sumproduct for month to date (ONLY WORKING DAYS)!

    Hi all

    Having some issues with sumproduct....the formula works fine, however for average purposes, because I am counting weekends, my average is skewed.

    can someone pls help me figure this one guy.

    =SUMPRODUCT(--($E$6:$J$6>=$C$26)*($E$6:$J$6<=$C$27)*($C$7:$C$10=$C20),$E$7:$J$10)/SUMPRODUCT(--($E$6:$J$6>=$C$26)*($E$6:$J$6<=$C$27)*($C$7:$C$10=$C20))

    My formula is saying, if start of month = C26
    and select date = C27
    then match name in range C7:C10 and give me the average

    as you can see in my attached worksheet, C16 is the date cell...and i am using C26 and C27 as helper cells, however the issue is, my formula does not factor in weekends.

    My answer in cell E20 should be 107.25 , however since my formula factors in all days, it gives 71.5

    can someone pls help me out? thxs alot.
    Attached Files Attached Files
    Last edited by jw01; 03-19-2013 at 09:58 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Sumproduct for month to date (ONLY WORKING DAYS)!

    Add the term

    *(WEEKDAY($E$6:$J$6,2)<=5)

    to both SPs, like this:

    =SUMPRODUCT(--($E$6:$J$6>=$C$26)*($E$6:$J$6<=$C$27)*($C$7:$C$10=$C20)*(WEEKDAY($E$6:$J$6,2)<=5),$E$7:$J$10)/SUMPRODUCT(--($E$6:$J$6>=$C$26)*($E$6:$J$6<=$C$27)*($C$7:$C$10=$C20)*(WEEKDAY($E$6:$J$6,2)<=5))

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    03-08-2013
    Location
    UK
    MS-Off Ver
    Excel Mutliple Version
    Posts
    7

    Re: Sumproduct for month to date (ONLY WORKING DAYS)!

    Hi JW01,
    Have you tried using the NETWEEKDAY function as this only calculates based on business days?

    clockWorX

  4. #4
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: Sumproduct for month to date (ONLY WORKING DAYS)!

    thx u so much sir...that works perfectly @Pete_UK

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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