Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with a #VALUE in a cell
In writing a formula, how will you skip the cell with a missing value, i.e.,
#VALUE? Example below. A B C D 1 Date opened Date closed # of days opened Area of responsibility 2 1/13/2006 1/31/2006 18 Case Line 3 1/17/2006 2/14/2006 28 Light Line 4 1/22/2006 - #VALUE! Case Line 5 2/10/2006 2/28/2006 18 Case Line I want to find the total # of days opened for Case Line. Note: Column C is the difference of C and B which is also written in a formula =SUM(B2-B1), etc. thus giving the value of column C4 #VALUE since B5 is still open. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with a #VALUE in a cell
=SUMIF(Range,"<#VALUE!")
however it's better to avoid the text (-) at all or use an if formula to return zero if not both cells have dates, then you can use a regular SUM(Range) Note that =SUM(B2-B1) is not necessary =B2=B1 will suffice Regards, Peo Sjoblom "FPJ" wrote: In writing a formula, how will you skip the cell with a missing value, i.e., #VALUE? Example below. A B C D 1 Date opened Date closed # of days opened Area of responsibility 2 1/13/2006 1/31/2006 18 Case Line 3 1/17/2006 2/14/2006 28 Light Line 4 1/22/2006 - #VALUE! Case Line 5 2/10/2006 2/28/2006 18 Case Line I want to find the total # of days opened for Case Line. Note: Column C is the difference of C and B which is also written in a formula =SUM(B2-B1), etc. thus giving the value of column C4 #VALUE since B5 is still open. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT with a #VALUE in a cell
Apologize, I can'r see how you table look since on my end it looks like you
have both dates and names in A? If not so try =SUM(IF(A2:A20="case line",IF(ISNUMBER(C2:C20),C2:C20))) entered with ctrl + shift & enter adapt to fit your real ranges Regards, Peo Sjoblom "Peo Sjoblom" wrote: =SUMIF(Range,"<#VALUE!") however it's better to avoid the text (-) at all or use an if formula to return zero if not both cells have dates, then you can use a regular SUM(Range) Note that =SUM(B2-B1) is not necessary =B2=B1 will suffice Regards, Peo Sjoblom "FPJ" wrote: In writing a formula, how will you skip the cell with a missing value, i.e., #VALUE? Example below. A B C D 1 Date opened Date closed # of days opened Area of responsibility 2 1/13/2006 1/31/2006 18 Case Line 3 1/17/2006 2/14/2006 28 Light Line 4 1/22/2006 - #VALUE! Case Line 5 2/10/2006 2/28/2006 18 Case Line I want to find the total # of days opened for Case Line. Note: Column C is the difference of C and B which is also written in a formula =SUM(B2-B1), etc. thus giving the value of column C4 #VALUE since B5 is still open. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
copying cell names | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |