Summing part of a string excluding cells
 

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 "X".
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 "if_then_" format but I am
stuck on how to do this?

Cheers
Soph

Summing part of a string excluding cells
 

You could use a "real" array formula:

=SUM(IF(LEN(I9:I32)<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:

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 "X".
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 "if_then_" format but I am
stuck on how to do this?

Cheers
Soph

--

Dave Peterson

Summing part of a string excluding cells
 

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

"Dave Peterson" wrote:

You could use a "real" array formula:

=SUM(IF(LEN(I9:I32)<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:

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 "X".
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 "if_then_" format but I am
stuck on how to do this?

Cheers
Soph

--

Dave Peterson