Networkdays query particular, following, network, however

andy

Hi,

I have searched the forums but don't think anyone has this particular problem.

I want to use networkdays in a sumproduct however it is based on a range. So I only want to count the instances where the number of network days between 2 dates is > 30.

So when I do this
=(NETWORKDAYS(OFFSET(HRevFailure,1,0,CaseCount,1)),Today(),NonWorkingDays))
I get a failure even though HRevFailure is a date.

So I split it out to the following
=(NETWORKDAYS(DATE(YEAR(OFFSET(HRevFailure,1,0,CaseCount,1)),MONTH(OFFSET(HRevFailure,1,0,CaseCount,1)),DAY(OFFSET(HRevF  ailure,1,0,CaseCount,1))),TODAY(),NonWorkingDays) > 35)

which then evaluates to true or false

However when I add a sumproduct around it as below
=SUMPRODUCT((NETWORKDAYS(DATE(YEAR(OFFSET(HRevFailure,1,0,CaseCount,1)),MONTH(OFFSET(HRevFailure,1,0,CaseCount,1)),DAY(O  FFSET(HRevFailure,1,0,CaseCount,1))),TODAY(),NonWorkingDays) > 35))

It fails. So my question is can NETWORKDAYS be used in a sumproduct and if so can I split the date function with offset ranges and will it still work or will it go through the year range then the month range then the date range.

I hope this makes sense.

No, NETWORKDAYS cannot be used in an array formula.  Create a helper column to individually calculate NETWORKDAYS for each row.  Then base your SUMPRODUCT formula on the helper column.  Note that if you have no other conditions to be evaluated, COUNTIF would suffice.

Hope this helps!


Originally Posted by DomenicNo, NETWORKDAYS cannot be used in an array formula.  Create a helper column to individually calculate NETWORKDAYS for each row.  Then base your SUMPRODUCT formula on the helper column.  Note that if you have no other conditions to be evaluated, COUNTIF would suffice.

Hope this helps!

Thanks will give it a go.

You could also try this formula, without a helper column
=COUNTIF(OFFSET(HRevFailure,1,0,CaseCount,1),"

Originally Posted by barry houdiniYou could also try this formula, without a helper column
=COUNTIF(OFFSET(HRevFailure,1,0,CaseCount,1),"

Its another part of the mammoth SUMPRODUCT you helped me with yesterday so not sure if I could get that into a sumproduct.

It can be part of SUMPRODUCT too, i.e.
=SUMPRODUCT(--(OFFSET(HRevFailure,1,0,CaseCount,1)

Originally Posted by barry houdiniIt can be part of SUMPRODUCT too, i.e.
=SUMPRODUCT(--(OFFSET(HRevFailure,1,0,CaseCount,1)

Thanks a lot Barry, I shall try and give it ago.