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

Find more posts tagged with

Formulas

Project Management

Accepted answers

All comments

12vanblart

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.

JMadd

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?

Paul Newcome

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.

JMadd

@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!

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.