Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 3 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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