How to add (sum) a row cells with letter

  • Thread starter Thread starter JCPS
  • Start date Start date
J

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.
 
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.
Click to expand...

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.
Click to expand...

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

try with something like above and...

if any doubt (or further information)... would you please comment ?
regards,
hector.
 
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".
 
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.
Click to expand...

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)
 
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
 
Max said:
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
Click to expand...

......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
---
 
Max said:
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.
Click to expand...

http://www.megaupload.com/es/?d=UPZXUDGR
 
Héctor Miguel said:
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...
Click to expand...
The other one;
=SUM(IF(LEFT(A1:D1)="S",IF(ISNUMBER(--MID(A1:D1,2,10)),--MID(A1:D1,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 said:
The other one;
=SUM(IF(LEFT(A1:D1)="S",IF(ISNUMBER(--MID(A1:D1,2,10)),--MID(A1:D1,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.
Click to expand...
 
Back
Top