Sumproduct with Substitute <p>I have a range, containing numeric values &amp; blank cells. Some of the numeric values will have * as suffix.</p> <pre><code>10* 5 7 9 25* 10 </code></pre> <p>When I do a SUM(A1:A8), I get result of 5+7+9+10 = 31, the required output. Now, I also require the total sum irrespective of * suffix. I'm trying to solve by using</p> <pre><code>SUMPRODUCT(SUBSTITUTE(A1:A8,"*","")) </code></pre> <p>It works out to</p> <pre><code>SUMPRODUCT({"10","5","7","","9","","25","10"}) </code></pre> <p>And outputs 0 since all are text values. When I use </p> <pre><code>SUMPRODUCT(value(SUBSTITUTE(A1:A8,"*",""))) </code></pre> <p>It works out to </p> <pre><code>SUMPRODUCT({10,5,7,#VALUE!,9,#VALUE!,25,10}) </code></pre> <p>and finally outputs #Value!. Can someone help me to solve this? Thanks for your time.</p>