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
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!