Using a conditional function to sum the difference between two col
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.
|