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.
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
Hi Dave,
Sorry not to made a clear statement. Perhaps the attached will help to illustrate the situation.
TRy
=SUMPRODUCT((B5:F5)*($B$3:$F$3<>$K$3))
=SUMPRODUCT((B6:F6)*($B$3:$F$3<>$K$3))
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?
Try
=SUMPRODUCT((B5:F5),--(ISNA(MATCH($B$3:$F$3,$K$3:$K$4,0))))
Holidays in K3:K4
Re: How to sum data for a range of date excluding holidays?
Hi John,
It's work fine. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks