JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
First time I've used array-entered 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 array-enetered formulae and not do this!
Any help please?
Thanks,
Jack
First time I've used array-entered 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 array-enetered formulae and not do this!
Any help please?
Thanks,
Jack