JackDanIce
Wellknown Member
 Joined
 Feb 3, 2010
 Messages
 9,696
 Office Version

 365
 Platform

 Windows
Hi,
First time I've used arrayentered formulas and struggling to work out what's wrong with this formula:
{=SUM(IF(LEFT(A1:A6,2)="sd",VALUE(RIGHT(A1:A6,LEN(A1:A6)2)),IF(ISNUMBER(VALUE(A1:A6)),A1:A6,"")))}
To explain, I have a list of values in fixed range A1:A6. These values are either numerical or alphanumeric. If they're alphanumerica, they will begin with "SD" or some other characters, but each cell value will always end in numerical characters
I want to add all these values up in a single cell (C1). I could use the B column to do the above on each single cell (e.g. A1 then copy down to A6) and use sum(B1:B6) but I'm trying to learn how to use arrayenetered formulae and not do this!
Any help please?
Thanks,
Jack
First time I've used arrayentered formulas and struggling to work out what's wrong with this formula:
{=SUM(IF(LEFT(A1:A6,2)="sd",VALUE(RIGHT(A1:A6,LEN(A1:A6)2)),IF(ISNUMBER(VALUE(A1:A6)),A1:A6,"")))}
To explain, I have a list of values in fixed range A1:A6. These values are either numerical or alphanumeric. If they're alphanumerica, they will begin with "SD" or some other characters, but each cell value will always end in numerical characters
I want to add all these values up in a single cell (C1). I could use the B column to do the above on each single cell (e.g. A1 then copy down to A6) and use sum(B1:B6) but I'm trying to learn how to use arrayenetered formulae and not do this!
Any help please?
Thanks,
Jack