Array formula
 

I am using an array funtion to sum a numbers followed by a letter. Example
3H,4H, etc...,
The formula looks like this:
=SUM(IF(RIGHT(C4:AD4,1)="H",--LEFT(C4:AD4,LEN(C4:AD4)-1)))

How can I add numbers followed by two letters. Example 3CT, 4CT, etc...
What do I have to do different in the function.

Thanks

Array formula
 

Always CT?

=SUM(IF(RIGHT(C4:AD4,2)="ct",--LEFT(C4:AD4,LEN(C4:AD4)-2)))

Notice that the =right() was changed to look at 2 characters and the len() now
had 2 subtracted from it.

JP wrote:

I am using an array funtion to sum a numbers followed by a letter. Example
3H,4H, etc...,
The formula looks like this:
=SUM(IF(RIGHT(C4:AD4,1)="H",--LEFT(C4:AD4,LEN(C4:AD4)-1)))

How can I add numbers followed by two letters. Example 3CT, 4CT, etc...
What do I have to do different in the function.

Thanks

--

Dave Peterson

Array formula
 

I am getting a #VALUE error with this. Whta could be going on?

Thanks

"Dave Peterson" wrote:

Always CT?

=SUM(IF(RIGHT(C4:AD4,2)="ct",--LEFT(C4:AD4,LEN(C4:AD4)-2)))

Notice that the =right() was changed to look at 2 characters and the len() now
had 2 subtracted from it.

JP wrote:

I am using an array funtion to sum a numbers followed by a letter. Example
3H,4H, etc...,
The formula looks like this:
=SUM(IF(RIGHT(C4:AD4,1)="H",--LEFT(C4:AD4,LEN(C4:AD4)-1)))

How can I add numbers followed by two letters. Example 3CT, 4CT, etc...
What do I have to do different in the function.

Thanks

--

Dave Peterson

Array formula
 

I figured it out. I one of the cells there was only text causing the error.
Thanks for the help.



"JP" wrote:

I am getting a #VALUE error with this. Whta could be going on?

Thanks

"Dave Peterson" wrote:

Always CT?

=SUM(IF(RIGHT(C4:AD4,2)="ct",--LEFT(C4:AD4,LEN(C4:AD4)-2)))

Notice that the =right() was changed to look at 2 characters and the len() now
had 2 subtracted from it.

JP wrote:

I am using an array funtion to sum a numbers followed by a letter. Example
3H,4H, etc...,
The formula looks like this:
=SUM(IF(RIGHT(C4:AD4,1)="H",--LEFT(C4:AD4,LEN(C4:AD4)-1)))

How can I add numbers followed by two letters. Example 3CT, 4CT, etc...
What do I have to do different in the function.

Thanks

--

Dave Peterson