How to add (sum) a row cells with letter

Try this:

...........A..........B..........C..........D
1......L2.3......S3.........W.......L3.3

Assuming there will *always* be a number associated with the letters "S" and
"L".

Array entered** :

=SUM(IF(LEFT(A1:D1)="L",--MID(A1:D1,2,10)))
=SUM(IF(LEFT(A1:D1)="S",--MID(A1:D1,2,10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

On a side note, why would you even keep a timesheet in this manner? You
should use separate cells to record "S" and "L".

--
Biff
Microsoft Excel MVP


"JCPS" <JCPS.TakeThisOut@discussions.microsoft.com> wrote in message
news:A6ACCE4B-E3E6-4FE2-A120-C17AB9E9B55D@microsoft.com...
> I'm trying to add cells WITH numbers but they have letters
> (alphanumerical).
> Example: A B C D E Total of sick days
> (S) Total vacations (L)
> L2.3 S3 W L3.3 - ..... 3
> 5.5
> L1.2 - - - -
> 1.2
> I'd like to sum just numbers of previous cells of A to E and total them at
> the last columns.
> By the way, if conditional formula may concider like the W letter but
> with a specific value like 7.2 and add it to a previos total. The same for
> S
> and L.
>

Hello Héctor!

I forgot about this portion in my reply:

>>if conditional formula may concider like the W letter but with a specific
>>value like 7.2 and add it to a previos total. The same for S and L.

>(i.e.) =countif(a2:e2,"w")*7.2
>The same for S and L.

If there will be only letters "S" and "L" in some cells then we need to
modify our formulas (which are just about identical to each other):

Still array entered** :

=SUM(IF(LEFT(A1:D1)="S",IF(ISNUMBER(--MID(A1:D1,2,10)),--MID(A1:D1,2,10))))

If cells contain just "S" or "L", --MID(A1:D1,2,10) generates an error.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Héctor Miguel" <NOhemiordiSPAM.RemoveThis@PLShotmail.com> wrote in message
news:O%23K1sf1%23IHA.524@TK2MSFTNGP06.phx.gbl...
> hi, !
>
>> I'm trying to add cells WITH numbers but they have letters
>> (alphanumerical).
>> Example: A B C D E Total of sick days
>> (S) Total vacations (L)
>> L2.3 S3 W L3.3 - ..... 3
>> 5.5
>> L1.2 - - - -
>> 1.2
>> I'd like to sum just numbers of previous cells of A to E and total them
>> at the last columns.
>
> sick days: =sum(if(left(a2:e2,1)="s",--mid(a2:e2,2,10)))
> vacations: =sum(if(left(a2:e2,1)="l",--mid(a2:e2,2,10)))
>
>> ... if conditional formula may concider like the W letter but with a
>> specific value like 7.2 and add it to a previos total.
>
> (i.e.) =countif(a2:e2,"w")*7.2
>
>> The same for S and L.
>
> try with something like above and...
>
> if any doubt (or further information)... would you please comment ?
> regards,
> hector.
>

hi, Sir !

> I forgot about this portion in my reply:
>
>>> if conditional formula may concider like the W letter but with a specific value like 7.2 and add it to a previos total. The same for S and L.
>
>> (i.e.) =countif(a2:e2,"w")*7.2
>> The same for S and L.

and I forgot to mention about array formulae )

best wishes,
hector.

__ OP __
> If there will be only letters "S" and "L" in some cells then we need to modify our formulas (which are just about identical to each other):
>
> Still array entered** :
>
> =SUM(IF(LEFT(A1:D1)="S",IF(ISNUMBER(--MID(A1:D1,2,10)),--MID(A1:D1,2,10))))
>
> If cells contain just "S" or "L", --MID(A1:D1,2,10) generates an error.
>
> ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)
>
> --
> Biff
> Microsoft Excel MVP

>> hi, !
>>
>>> I'm trying to add cells WITH numbers but they have letters (alphanumerical).
>>> Example: A B C D E Total of sick days (S) Total vacations (L)
>>> L2.3 S3 W L3.3 - ..... 3 5.5
>>> L1.2 - - - - 1.2
>>> I'd like to sum just numbers of previous cells of A to E and total them at the last columns.
>>
>> sick days: =sum(if(left(a2:e2,1)="s",--mid(a2:e2,2,10)))
>> vacations: =sum(if(left(a2:e2,1)="l",--mid(a2:e2,2,10)))
>>
>>> ... if conditional formula may concider like the W letter but with a specific value like 7.2 and add it to a previos total.
>>
>> (i.e.) =countif(a2:e2,"w")*7.2
>>
>>> The same for S and L.
>>
>> try with something like above and...
>>
>> if any doubt (or further information)... would you please comment ?

Another play to tinker ..
Source data (ie L2.3, S3, W, L3.3 etc) assumed in B2:H2 down
List the 3 letters into J1:L1 : L, S, W (Must be in caps, it's case
sensitive here)
Then paste this into J2's formula bar and press CTRL+SHIFT+ENTER to confirm
the formula (ie array-enter the formula):
=SUM(IF(ISNUMBER(SUBSTITUTE($B2:$H2,J$1,"")+0),SUBSTITUTE($B2:$H2,J$1,"")+0))+COUNTIF($B2:$H2,J$1)
Copy J2 across to L2, fill down as far as required
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik
---
"JCPS" wrote:
> I'm trying to add cells WITH numbers but they have letters
> (alphanumerical).
> Example: A B C D E Total of sick days
> (S) Total vacations (L)
> L2.3 S3 W L3.3 - ..... 3
> 5.5
> L1.2 - - - -
> 1.2
> I'd like to sum just numbers of previous cells of A to E and total them at
> the last columns.
> By the way, if conditional formula may concider like the W letter but
> with a specific value like 7.2 and add it to a previos total. The same for S
> and L.
>

"Max" wrote:

> Another play to tinker ..
> Source data (ie L2.3, S3, W, L3.3 etc) assumed in B2:H2 down
> List the 3 letters into J1:L1 : L, S, W (Must be in caps, it's case
> sensitive here)
> Then paste this into J2's formula bar and press CTRL+SHIFT+ENTER to confirm
> the formula (ie array-enter the formula):
> =SUM(IF(ISNUMBER(SUBSTITUTE($B2:$H2,J$1,"")+0),SUBSTITUTE($B2:$H2,J$1,"")+0))+COUNTIF($B2:$H2,J$1)
> Copy J2 across to L2, fill down as far as required
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:17,000 Files:358 Subscribers:55
> xdemechanik
> ---
> "JCPS" wrote:
> > I'm trying to add cells WITH numbers but they have letters
> > (alphanumerical).
> > Example: A B C D E Total of sick days
> > (S) Total vacations (L)
> > L2.3 S3 W L3.3 - ..... 3
> > 5.5
> > L1.2 - - - -
> > 1.2
> > I'd like to sum just numbers of previous cells of A to E and total them at
> > the last columns.
> > By the way, if conditional formula may concider like the W letter but
> > with a specific value like 7.2 and add it to a previos total. The same for S
> > and L.
> > Thanks, have been useful. Right now I'm looking for a formula if I have a column for a remark like "No Paid" using a letter W in substitute, but at the same time may discriminate from others like M or F, and give a result of hours used for those kind of license on a column beside this one.

......monday tuesday wednesday ..................Use Hrs---License Letter
........L3.2.......W...........F...................................7.2.............W
or
...... -
.........M...........W..................................7.2.............M
................................................................(and).7.2.............W this both at the same day (could be on different column).

Lost you on your new query ..

Can you upload a sample file (in .xls format) using a free filehost,
then post a link to it here?

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik
---
"JC-PS" <JCPS RemoveThis @discussions.microsoft.com> wrote
>> > Thanks, have been useful. Right now I'm looking for a formula if I
>> > have a column for a remark like "No Paid" using a letter W in
>> > substitute, but at the same time may discriminate from others like M or
>> > F, and give a result of hours used for those kind of license on a
>> > column beside this one.
>
> .....monday tuesday wednesday ..................Use Hrs---License Letter
> ......L3.2.......W...........F...................................7.2.............W
> or
> ..... -
> ........M...........W..................................7.2.............M
> ..............................................................(and).7.2.............W
> this both at the same day (could be on different column).