How to add (sum) a row cells with letter

[JCPS]

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.

 

[Héctor Miguel]

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.

 

[T. Valko]

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(A11)="L",--MID(A11,2,10)))
=SUM(IF(LEFT(A11)="S",--MID(A11,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".

 

[T. Valko]

Hello Héctor!

I forgot about this portion in my reply:

(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(A11)="S",IF(ISNUMBER(--MID(A11,2,10)),--MID(A11,2,10))))

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

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

 

[Héctor Miguel]

hi, Sir !

I forgot about this portion in my reply:

and I forgot to mention about array formulae )

best wishes,
hector.

__ OP __

 

[Max]

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

 

[JC-PS]

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

......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).

 

[Max]

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]

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
---



This is a portion of the table pasted I'm working on, I hope may be lightful.

http://www.megaupload.com/es/?d=UPZXUDGR

 

[Max]

Unable to download your sample as per the link given

Try using the earlier simple, free filehost to upload:
http://www.freefilehosting.net/

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---

 

[JC-PS]

Unable to download your sample as per the link given

Try using the earlier simple, free filehost to upload:
http://www.freefilehosting.net/

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---



Try this time, I've been using the language version for the others pads. This time should work out well or better...

http://www.megaupload.com/?d=UPZXUDGR

 

[JC-PS]

hi, !


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)))


(i.e.) =countif(a2:e2,"w")*7.2


try with something like above and...

if any doubt (or further information)... would you please comment ?
regards,
hector.


The formula =SUM(IF(LEFT(C51:N51,1)="s",--MID(C51:N51,2,10)))=COUNTIF(A2:E2,"s")*7.2 shows #VALUE! with CSE or without =countif...

The other one;
=SUM(IF(LEFT(A11)="S",IF(ISNUMBER(--MID(A11,2,10)),--MID(A11,2,10))))
enter with CSE shows or add only S with numbers, but no just the S alone
which must be included in the formula for the value of 7.5.
If I have in the 2 Row, S3.5 in A2, and S in C2, and other letter meaning
for vacation "L" in E2, it should end up counting just the S including those
alphanumerical and exclude other letters. So the result must be 11. I hope
this time I expressed it better.

 

[JC-PS]

The other one;
=SUM(IF(LEFT(A11)="S",IF(ISNUMBER(--MID(A11,2,10)),--MID(A11,2,10))))
enter with CSE shows or add only S with numbers, but no just the S alone
which must be included in the formula for the value of 7.5.
If I have in the 2 Row, S3.5 in A2, and S in C2, and other letter meaning
for vacation "L" in E2, it should end up counting just the S including those
alphanumerical and exclude other letters. So the result must be 11. I hope
this time I expressed it better.