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!

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!

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!

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!