Sum the difference between dates given a certain criteria

[Detroit David]

I have a workbook with several worksheets (tabs)

The first and main worksheet has eight (8) columns with N rows.

I wish to calculate the number of days between two date values and sum the
results for rows that meet a certain criteria.

Lets use a simple example (so I can understand)

Three columns as follows:

A. Start Date
B. Due Date
C. Criteria

If the word in column C is “Done†than calculate the number of days between
A & B and add it to a sum.

A B C
Start Date Due Date Status
01/01/08 01/31/08 Done
02/01/08 04/15/08 Done
03/01/08 05/10/08 Open
04/01/08 06/15/08 Open
05/02/08 06/15/08 Done

Thank you in advance for your help.

Detroit David

 

[T. Valko]

Try this:

=SUMPRODUCT(--(C2:C6="done"),B2:B6-A2:A6)

 

[Detroit David]

Thank you for the prompt and accurate reply it worked perfectly.... however I
forgot one element which I have not been able to get to work.

I wish to calculate using "NETWORKDAYS" I have tried everyway I can think of
to get the formula to work with "NETWORKDAYS" but I haven't found the
solution.

Could you please tell me how to use NETWORKDAYS in this type of calculation.

Thank you

 

[T. Valko]

NETWORKDAYS won't work with arrays so you'd have to do something like this:

=IF(C2="done",NETWORKDAYS(A2,B2,holidays),"")

Copy down as needed.

Then use a SUM formula on that column.

 

[Detroit David]

Thank you for your help.

NETWORKDAYS won't work with arrays so you'd have to do something like this:

=IF(C2="done",NETWORKDAYS(A2,B2,holidays),"")

Copy down as needed.

Then use a SUM formula on that column.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you for your help.