Sum the difference between dates given a certain criteria
 

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

Sum the difference between dates given a certain criteria
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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

Sum the difference between dates given a certain criteria
 

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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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

Sum the difference between dates given a certain criteria
 

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.

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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

Sum the difference between dates given a certain criteria
 

Thank you for your help.



"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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

Sum the difference between dates given a certain criteria
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
Thank you for your help.



"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in
message
...
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