COUNTIF/SUMIF items with number of workdays between a date and a date range is 3
Hi!
I would like to count a number of entries in a sheet if their due date is a certain number of work days before a date in another sheet. I tried to use the following formula:
=COUNTIF('reference to a list of entries column in another sheet', NETDAYS('reference to due date column in another sheet', 'cell with a date in a current sheet') = 3)
Unfortunately, it always gives 0 as result.
Is it possible to fix this formula or use another one for this task?
Best,
Lena
Answers

When using COUNTIF, you need to be comparing something to the 'range' variable you are passing in. Try this modified version of your function:
=COUNTIF(NETDAYS('reference to due date column in another sheet', 'cell with a date in a current sheet'), =3)
I also believe that instead of NETDAYS you want to use either NETWORKDAY or NETWORKDAYS so that weekends aren't being counted in the returned result.

Hi, similar question  I'd like to sum the net work days between 2 dates on a referenced sheet, with criteria of if the job is complete, month and year.
This is my formula for March, 2023 and am getting the #INVALIDDATATYPE error.
=SUMIFS(NETWORKDAYS({Start}, {Finish}), {Task Name}, (@cell) = "HST", {% Complete}, =1, {Finish}, YEAR(@cell) = 2023, {Finish}, MONTH(@cell) = 3)
I was initially using SUMIFS for the duration column but, really need to add only net working days. Is there a work around or is my formula incorrect?

The original post can be solved using a @cell reference.
=COUNTIFS({Other Sheet Date Column}, NETWORKDAYS(@cell, [Date Column name]@row)<= 3)
@JMadd Are you using dependencies in the source sheet? If so, the duration column is automatically set to be working days, so you wouldn't need to use a specific function for that. Just the SUMIFS on the duration column.
If the Duration column is not being used through the dependency settings and it is not populating a duration in workdays, then you will need to insert a column on the source sheet that calculates the number of working days between start and finish on every row then use your cross sheet SUMIFS on this new column.

@Paul Newcome I am using the dependencies column but, not for every child underneath the parent row I'm trying to calculate. Your last suggestion is a great option, thank you!

If dependencies are turned on, then they are turned on for the entire column. Do you mean you are just not entering a duration in some of the child rows? I'm not sure I understand your comment. Sorry.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!