Within another function calculate and sum number of days

[Detroit David]

How can a use a function within a function:

The spread sheet has three columns for this example:
A - Start Date
B - End Date
C - Status

For those rows that match the status criteria "Done" I wish to calculate the
number of networkdays between the Start and End dates and add it to a total.

I have tried "SUMIF" but "Sum_range" does not seem to accept the calculation.

Thank you in advance for your help.

 

[Gaurav]

=IF(C1="done",NETWORKDAYS(A1,B1)+SUM(D210),SUM(D210))

The ranges are hypothetical...you can change as per your need.

Thanks

 

[Detroit David]

Thank you for your help, however I could not get the formula to work.
I set up a simple matrix (see below) and tried to use the formula on it.
Note: the column to the right of “D†has the “network days†calculated for
verification

The formula as written did not seem to work I tried several variations of
it. I tried a few variations but nothing worked. If I changed the date values
in the first set the result in formula 2 would change to that value.

=IF(C1="Done",NETWORKDAYS(A2,B2)+SUM(D210),SUM(D210))
Gave a result of “0â€


=IF(C2="Done",NETWORKDAYS(A2,B2)+SUM(D210),SUM(D210))
Gave a result of “5â€

It only calculates the first line, how does one establish a range (i.e.
C1:C10)

What am I doing wrong?

Thank you for you patience.



A B C D
1 Date End Date Status
2 02/06/08 02/12/08 Done 5
3 02/06/08 02/14/08 Done 7
4 02/06/08 02/26/08 Done 15
5 03/05/08 On-Hold
6 03/05/08 03/28/08 Done 18
7 03/05/08 On-Hold
8 03/05/08 On-going
9 03/05/08 On-going
10 03/05/08 04/03/08 Done 22
-------
67

Detroit David

 

[Gaurav]

What formula you want to add the days to? I just used SUM(D210) as an
example.

 

[Detroit David]

On a separate page (Tab) in the worksheet I wish to insert a number that is
the total networkdays for the rows meeting the criteria.

In the example there were five rows that have the status of "Done"
The network days for these five total 67.

I wish use that number (a single value, in this case 67) in a table on
another tab of the worksheet.

Therefore I want the formula to:
select the rows that meet the criteria,
calculate the number of network days for that row
add that row's days to a summary total and display it.

I am sorry if I did not make it clear before.

Thank you again for the help.

Detroit David

 

[Gaurav]

You can put this formula in E2

=IF(D2="DONE".NETWORKDAYS(A2:B2),"")

Copy it down till...say E7. Now go to Sheet2 and in the cell where you want
the SUM, enter =SUM(E2:E7)

would that help?

 

[Detroit David]

Thank you for your help. That will work.

However, I thought that there might be a more elegant solution that would be
one formula without an intermediate step.

Thank you again for the help.