3

I have a list of date ranges, let's say:

        A                   B 
1 April 25, 2013     May 31, 2013
2 September 30, 2013 October 18, 2013

I need to calculate a sum of net working days of all ranges into a single cell. I want to avoid intermediate =NETWORKDAYS(). An array formula is a logical choice, but an attempt to apply it, like this {=SUM(NETWORKDAYS(A1:A2, B1:B2))} leads to a formula error.

How can it be done?

More broadly, how does Excel apply an array formula? It is not clear to me from basic array formula examples.

6

Try this version

=SUMPRODUCT(NETWORKDAYS(A1:A2+0, B1:B2+0))

NETWORKDAYS accepts "arrays" but not ranges (in Excel 2007 or later) so the +0 converts the range to an array

SUMPRODUCT instead of SUM means you don't need to "array enter" the formula

If you use SUM then you "array enter" by doing this:

1) Select cell with formula

2) Press F2 key to select formula

3) Hold down CTRL and SHIFT keys and press ENTER

if done correctly you get { and } around formula

1
  • I like SUMPRODUCT version. – DKroot Jun 19 '13 at 3:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.