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

Lena
Lena
edited 12/09/19 in Formulas and Functions

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. 

  • JMadd
    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
    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
    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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!