Using a conditional function to sum the difference between two col

  • Thread starter Thread starter Detroit David
  • Start date Start date
D

Detroit David

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:A6>0,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
Click to expand...


=SUMPRODUCT(--(B1:B6<>""),B1:B6-A1:A6)
 
Thank you for worked perfectly.


T. Valko said:
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:



=SUMPRODUCT(--(B1:B6<>""),B1:B6-A1:A6)
Click to expand...
 
Back
Top