Array Formula - using LEFT("text",4) in formula
 

I have the array formula {=SUM(IF((C2:C1440="ALL")*(A2:A1440="R05"),M2:M144 0,
0))}

It would work fine, except that the portion "R05" is really just a portion of
the field value. The field will really contain values like, R05222105,
R0522106, R0342100, etc. I only want to SUM values if the LEFT three
characters are "R05".

I've tried {=SUM(IF((C2:C1440="ALL")*LEFT(A2:A1440="R05"),3), M2:M1440,0))}
but this doesn't work. Is what I'm trying to do possible? If so, what is
the syntax?

Thanks in advance,
Andrew


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1

Try...

=SUM(IF((C2:C1440="ALL")*(LEFT(A2:A1440,3)="R05"), M2:M1440))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Andrew L via OfficeKB.com" wrote:

I have the array formula {=SUM(IF((C2:C1440="ALL")*(A2:A1440="R05"),M2:M144 0,
0))}

It would work fine, except that the portion "R05" is really just a portion of
the field value. The field will really contain values like, R05222105,
R0522106, R0342100, etc. I only want to SUM values if the LEFT three
characters are "R05".

I've tried {=SUM(IF((C2:C1440="ALL")*LEFT(A2:A1440="R05"),3), M2:M1440,0))}
but this doesn't work. Is what I'm trying to do possible? If so, what is
the syntax?

Thanks in advance,
Andrew

It works like a charm. Thank you.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200508/1