software

Hi

Sorry, I hit enter on my last post before I finished!
My problem is that in a column that I am trying to sum, most cells have the
format c1.3 (1 letter and 3 digit spaces) but some cells simply have an quot;Xquot;.
I tried to use the formula advised to Arturo:
=SUMPRODUCT(--(RIGHT(I9:I32,3))) but I need to somehow exclude the X's or
count them as 0. I assume this needs to be an quot;if_then_quot; format but I am
stuck on how to do this?

Cheers
SophYou could use a quot;realquot; array formula:

=SUM(IF(LEN(I9:I32)lt;4,0,--(RIGHT(I9:I32,3))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

soph wrote:
gt;
gt; Hi
gt;
gt; Sorry, I hit enter on my last post before I finished!
gt; My problem is that in a column that I am trying to sum, most cells have the
gt; format c1.3 (1 letter and 3 digit spaces) but some cells simply have an quot;Xquot;.
gt; I tried to use the formula advised to Arturo:
gt; =SUMPRODUCT(--(RIGHT(I9:I32,3))) but I need to somehow exclude the X's or
gt; count them as 0. I assume this needs to be an quot;if_then_quot; format but I am
gt; stuck on how to do this?
gt;
gt; Cheers
gt; Soph

--

Dave Peterson

Thanks dave!!! This is perfect, really appreciate the help )

quot;Dave Petersonquot; wrote:

gt; You could use a quot;realquot; array formula:
gt;
gt; =SUM(IF(LEN(I9:I32)lt;4,0,--(RIGHT(I9:I32,3))))
gt;
gt; This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
gt; correctly, excel will wrap curly brackets {} around your formula. (don't type
gt; them yourself.)
gt;
gt; soph wrote:
gt; gt;
gt; gt; Hi
gt; gt;
gt; gt; Sorry, I hit enter on my last post before I finished!
gt; gt; My problem is that in a column that I am trying to sum, most cells have the
gt; gt; format c1.3 (1 letter and 3 digit spaces) but some cells simply have an quot;Xquot;.
gt; gt; I tried to use the formula advised to Arturo:
gt; gt; =SUMPRODUCT(--(RIGHT(I9:I32,3))) but I need to somehow exclude the X's or
gt; gt; count them as 0. I assume this needs to be an quot;if_then_quot; format but I am
gt; gt; stuck on how to do this?
gt; gt;
gt; gt; Cheers
gt; gt; Soph
gt;
gt; --
gt;
gt; Dave Peterson
gt;