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

1. ## 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. ## 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”.
• click “Close window”
• be sure to desensitize the data

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

3. ## 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.

4. ## 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. ## 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. ## 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. ## 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

• 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