Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
It works like a charm. Thank you. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200508/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with Array Formula | Excel Worksheet Functions | |||
How can you use count with an array formula similar to using sum | Excel Worksheet Functions | |||
Trouble shooting#NA error in Array formula | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
What instead of an array formula? | Excel Discussion (Misc queries) |