![]() |
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. |
Using a conditional function to sum the difference between two col
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. |
Using a conditional function to sum the difference between two
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. |
Using a conditional function to sum the difference between two
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. |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com