+ Reply to Thread
Results 1 to 7 of 7

How to sum data for a range of date excluding holidays?

  1. #1
    Registered User
    Join Date
    05-10-2017
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    14

    How to sum data for a range of date excluding holidays?

    Hi All,
    I need help for this scenario. I had a date range in column which show the number of hours. I would like to sum up the data but to exclude holidays. I tried formula below

    =IF(NETWORKDAYS($B$3,$F$3, $K$3),SUM(B4:F4)) but the return value is full range without to exclude the holidays.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,862

    Re: How to sum data for a range of date excluding holidays?

    Voong welcome to the forum.

    I am not clear enough on your meaning. I think we are going to need to see a desensitized sample workbook (*.xlsx file not a screen shot) that shows what you mean.

    Be sure to substitute fake data in place of anything sensitive.

    If you do not already know how to do this:

    To attach a file to your post,
    • click “Go Advanced” (next to Post Quick Reply – bottom right),
    • scroll down until you see “Manage Attachments”, click that,
    • click “Browse”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”
    • be sure to desensitize the data

    The file name will appear at the bottom of your reply.

    I am signing off for the night (in USA). Will look for your reply.
    Dave

  3. #3
    Registered User
    Join Date
    05-10-2017
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    14

    Re: How to sum data for a range of date excluding holidays?

    Hi Dave,
    Sorry not to made a clear statement. Perhaps the attached will help to illustrate the situation.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,771

    Re: How to sum data for a range of date excluding holidays?

    TRy

    =SUMPRODUCT((B5:F5)*($B$3:$F$3<>$K$3))

    =SUMPRODUCT((B6:F6)*($B$3:$F$3<>$K$3))

  5. #5
    Registered User
    Join Date
    05-10-2017
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    14

    Re: How to sum data for a range of date excluding holidays?

    Hi John,
    It worked!. For multiple holidays (means column to exclude), I used
    =SUMPRODUCT((B8:F8)*($B$3:$F$3<>$K$3)*($B$3:$F$3<>$L$3))
    Is there a simpler way to cater for multiple holidays?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    17,771

    Re: How to sum data for a range of date excluding holidays?

    Try

    =SUMPRODUCT((B5:F5),--(ISNA(MATCH($B$3:$F$3,$K$3:$K$4,0))))

    Holidays in K3:K4

  7. #7
    Registered User
    Join Date
    05-10-2017
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    14

    Smile Re: How to sum data for a range of date excluding holidays?

    Hi John,
    It's work fine. Thank you.

+ 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] sum value based on date...excluding weekends and holidays
    By greggatz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2015, 05:17 PM
  2. Date in a cell Excluding Holidays?
    By Blondie_SA in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2013, 11:40 AM
  3. Replies: 2
    Last Post: 06-06-2012, 02:07 PM
  4. display date, excluding weekends & holidays
    By emueller in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-10-2008, 03:55 PM
  5. trying to calculate regular days in date range excluding holidays
    By DKY in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2008, 09:12 PM
  6. Calculate A Date Excluding Weekends And Holidays
    By travelersway in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 02-08-2006, 09:36 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