Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 4 down vote accepted

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

share|improve this answer
    
+1, great explanation. – dav Jun 18 '13 at 19:32
    
I like SUMPRODUCT version. – DKroot Jun 19 '13 at 3:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .