Using a conditional function to sum the difference between two columns.

Column C has Issue dates
Column D has Due dates (ALWAYS later than Issue Dates C)

Task is to sum the difference between columns D and C but skip those rows
that have blank in column D.

I attempted to use €œIF€� and €œSUMPRODUCT€� in the following formula to add the
different between two column skipping those €œD€� cells that were blank. The
data in the cells are dates, however I also tried it with just plain whole
numbers and had a similar result.

If I use the formula on a single cell where the €œD€� cell is blank, i.e. the
range is €œD2:D2-C2:C2€� it will show a result of blank. Also it will work
when I expand the range until it includes a blank cell, then it subtracts the
negative value of the €œC€� cell from the total. The formula doesnt seem to
be skipping the rows with blank cells but rather subtracting the value of €œC€�.

The result below should be 87, however it comes back as -39397

€¦and use I used the Shift-Ctrl-Enter

{=IF(A$1:A60,SUMPRODUCT(B$1:B6-A$1:A6),"")}

A B
1 02/06/08 02/15/08
2 02/06/08 02/15/08
3 02/06/08 02/15/08
4 02/06/08
5 02/06/08 03/31/08
6 02/14/08 02/20/08

Thank you for your help.

Assuming that column A will *always* have a date when there is a date in
column B. In other words, you *won't* have something like this:

1 02/06/08 02/15/08
2 02/15/08
3 02/06/08 02/15/08


=SUMPRODUCT(--(B1:B6<""),B1:B6-A1:A6)

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
Using a conditional function to sum the difference between two columns.

Column C has Issue dates
Column D has Due dates (ALWAYS later than Issue Dates C)

Task is to sum the difference between columns D and C but skip those rows
that have blank in column D.

I attempted to use "IF" and "SUMPRODUCT" in the following formula to add
the
different between two column skipping those "D" cells that were blank.
The
data in the cells are dates, however I also tried it with just plain whole
numbers and had a similar result.

If I use the formula on a single cell where the "D" cell is blank, i.e.
the
range is "D2:D2-C2:C2" it will show a result of blank. Also it will work
when I expand the range until it includes a blank cell, then it subtracts
the
negative value of the "C" cell from the total. The formula doesn't seem
to
be skipping the rows with blank cells but rather subtracting the value of
"C".

The result below should be 87, however it comes back as -39397

.and use I used the Shift-Ctrl-Enter

{=IF(A$1:A60,SUMPRODUCT(B$1:B6-A$1:A6),"")}

A B
1 02/06/08 02/15/08
2 02/06/08 02/15/08
3 02/06/08 02/15/08
4 02/06/08
5 02/06/08 03/31/08
6 02/14/08 02/20/08

Thank you for your help.

Thank you for worked perfectly.


"T. Valko" wrote:

Assuming that column A will *always* have a date when there is a date in
column B. In other words, you *won't* have something like this:

1 02/06/08 02/15/08
2 02/15/08
3 02/06/08 02/15/08


=SUMPRODUCT(--(B1:B6<""),B1:B6-A1:A6)

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
Using a conditional function to sum the difference between two columns.

Column C has Issue dates
Column D has Due dates (ALWAYS later than Issue Dates C)

Task is to sum the difference between columns D and C but skip those rows
that have blank in column D.

I attempted to use "IF" and "SUMPRODUCT" in the following formula to add
the
different between two column skipping those "D" cells that were blank.
The
data in the cells are dates, however I also tried it with just plain whole
numbers and had a similar result.

If I use the formula on a single cell where the "D" cell is blank, i.e.
the
range is "D2:D2-C2:C2" it will show a result of blank. Also it will work
when I expand the range until it includes a blank cell, then it subtracts
the
negative value of the "C" cell from the total. The formula doesn't seem
to
be skipping the rows with blank cells but rather subtracting the value of
"C".

The result below should be 87, however it comes back as -39397

.and use I used the Shift-Ctrl-Enter

{=IF(A$1:A60,SUMPRODUCT(B$1:B6-A$1:A6),"")}

A B
1 02/06/08 02/15/08
2 02/06/08 02/15/08
3 02/06/08 02/15/08
4 02/06/08
5 02/06/08 03/31/08
6 02/14/08 02/20/08

Thank you for your help.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
Thank you for worked perfectly.


"T. Valko" wrote:

Assuming that column A will *always* have a date when there is a date in
column B. In other words, you *won't* have something like this:

1 02/06/08 02/15/08
2 02/15/08
3 02/06/08 02/15/08


=SUMPRODUCT(--(B1:B6<""),B1:B6-A1:A6)

--
Biff
Microsoft Excel MVP


"Detroit David" wrote in message
...
Using a conditional function to sum the difference between two columns.

Column C has Issue dates
Column D has Due dates (ALWAYS later than Issue Dates C)

Task is to sum the difference between columns D and C but skip those
rows
that have blank in column D.

I attempted to use "IF" and "SUMPRODUCT" in the following formula to
add
the
different between two column skipping those "D" cells that were blank.
The
data in the cells are dates, however I also tried it with just plain
whole
numbers and had a similar result.

If I use the formula on a single cell where the "D" cell is blank, i.e.
the
range is "D2:D2-C2:C2" it will show a result of blank. Also it will
work
when I expand the range until it includes a blank cell, then it
subtracts
the
negative value of the "C" cell from the total. The formula doesn't
seem
to
be skipping the rows with blank cells but rather subtracting the value
of
"C".

The result below should be 87, however it comes back as -39397

.and use I used the Shift-Ctrl-Enter

{=IF(A$1:A60,SUMPRODUCT(B$1:B6-A$1:A6),"")}

A B
1 02/06/08 02/15/08
2 02/06/08 02/15/08
3 02/06/08 02/15/08
4 02/06/08
5 02/06/08 03/31/08
6 02/14/08 02/20/08

Thank you for your help.