Count work days only

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198
Hello,

I am trying to count the number of days that fall into a working week (Mon-Fri). My dates are in range J6:NK6 and I want to count the number of times "S" appears in range J10:NK10 that is on a weekday only. But here is the tricky part I also need to exclude bank holidays that are in a named range called "Holidays".

So far I have managed to get the counting of the working week by using:
Code:
=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S"))
However I cannot get it to also not included bank holidays. I have tried
Code:
=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S")*($J6:$NK6<>Holiday))
But with no luck.

I think I am on the correct lines, but just need some help with the last bit.

Thank you,

John
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

johnbird1988

Board Regular
Joined
Oct 6, 2009
Messages
198
networkdays() will only count the number of days between two dates and exclude holidays. I want to count the number of "S" in the range and if a "S" is entered on a weekend or holiday then it not to be counted. networkdays will not do this.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try this version

=SUMPRODUCT((WEEKDAY($J6:$NK6,2)<6)*($J10:$NK10="S")*(COUNTIF(Holiday,$J6:$NK6)=0))

....or it is possible to use NETWORKDAYS like this

=SUMPRODUCT(NETWORKDAYS($J6:$NK6+0,$J6:$NK6+0,Holiday)*($J10:$NK10="S"))
 
Last edited:

owensy

New Member
Joined
Apr 17, 2015
Messages
10
help

count working days excel including weekends formula - =NETWORKDAYS(I7,J7) currently using this - but it won't count he weekends ! can you help me modify to include weekends please
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you want to count all days just subtract one from the other, e.g.

=J7-I7

format result cell as number

Depending on your definition you may need to add 1 (is today until tomorrow 1 day or 2?)
 

owensy

New Member
Joined
Apr 17, 2015
Messages
10
Re: hi Count work days only This gives me a minus figure tho ?

If you want to count all days just subtract one from the other, e.g.

=J7-I7

format result cell as number

Depending on your definition you may need to add 1 (is today until tomorrow 1 day or 2?)
hhh
 

owensy

New Member
Joined
Apr 17, 2015
Messages
10
Re: hi Count work days only This gives me a minus figure tho ?

This is giving a minus figure
 

Watch MrExcel Video

Forum statistics

Threads
1,099,078
Messages
5,466,484
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top