Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum, except #N/A
Hi, i have this code : =SUMPRODUCT(--SUBSTITUTE(C4:AA4,"Late ",""))
In some cells i have #N/A , and the code it's not working anymore. I need the code to sum everything but to ignore #N/A. Can this be done? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum, except #N/A
Hi Again
Try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(--SUBSTITUTE(IF(ISERROR(SEARCH("Late ",C4:S4)),0,C4:S4),"Late ","")) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code : =SUMPRODUCT(--SUBSTITUTE(C4:AA4,"Late ","")) In some cells i have #N/A , and the code it's not working anymore. I need the code to sum everything but to ignore #N/A. Can this be done? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum, except #N/A
Hi Jakob. After your code from last night, i need another kind of sum.
The same code: =SUMPRODUCT(--SUBSTITUTE(C4:AA4,"Late ","")) but to sum except "On Time". Thanks again! "Jacob Skaria" a scris: Hi Again Try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(--SUBSTITUTE(IF(ISERROR(SEARCH("Late ",C4:S4)),0,C4:S4),"Late ","")) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code : =SUMPRODUCT(--SUBSTITUTE(C4:AA4,"Late ","")) In some cells i have #N/A , and the code it's not working anymore. I need the code to sum everything but to ignore #N/A. Can this be done? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum, except #N/A
=SUMPRODUCT(--SUBSTITUTE(IF(ISERROR(SEARCH("Late ",C4:S4)),0,C4:S4),"Late
","")) Does the above work? I check the previosu post..and that was regarind returning text value right ?? Am i missing something... If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi Jakob. After your code from last night, i need another kind of sum. The same code: =SUMPRODUCT(--SUBSTITUTE(C4:AA4,"Late ","")) but to sum except "On Time". Thanks again! "Jacob Skaria" a scris: Hi Again Try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUMPRODUCT(--SUBSTITUTE(IF(ISERROR(SEARCH("Late ",C4:S4)),0,C4:S4),"Late ","")) If this post helps click Yes --------------- Jacob Skaria "puiuluipui" wrote: Hi, i have this code : =SUMPRODUCT(--SUBSTITUTE(C4:AA4,"Late ","")) In some cells i have #N/A , and the code it's not working anymore. I need the code to sum everything but to ignore #N/A. Can this be done? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|