# COUNTIF/SUMIF items with number of workdays between a date and a date range is 3

edited 12/09/19

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

• 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!