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

[Andrew L via OfficeKB.com]

I have the array formula {=SUM(IF((C2:C1440="ALL")*(A2:A1440="R05"),M2:M1440,
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

 

[Domenic]

Try...

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

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

Hope this helps!

 

[Andrew L via OfficeKB.com]

It works like a charm. Thank you.