Using a rang cells in multi functions (with empty cells)

[Na'il]

I want to use sum for rang of cells with some other conditions
cells : 8x , 8 , 55x , (empty cell)

=SUM(IF(FIND("x";C3:E3;1)<>0;LEFT(C3:E3;LEN(C3:E3)-1);C3:E3))

Not working!!

The answer must be 71 by that functions

 

[pecoflyer]

Hello to you too
Try =SUMPRODUCT(--(SUBSTITUTE(C3:E3;"x";"")))

And BTW do NOT used merged cells, they are nothing but trouble

 

[Na'il]

It's works , thank you

 

[Na'il]

But if there is an empty cell ??
it will not work !

@pecoflyer

 

[bosco_yip]

Try, to use Text function to force empty cell into 0 value.

The formula become :

=SUMPRODUCT(--SUBSTITUTE(TEXT(C3:F3,"0;@"),"x",""))