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.

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.

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" wrote in message
...
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 ?

"Héctor Miguel" wrote:

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.


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(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" wrote:



"Héctor Miguel" wrote:

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.


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

I posted the table I'm trying to finished hardly so far, and you
may see it on:
http://www.megaupload.com/?d=2ZI47CUS.
I hope might be helpful illustrated with laterals explanations and questions.

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" wrote in message
...
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.

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),SUB STITUTE($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),SUB STITUTE($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" 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).

"Max" wrote:

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


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

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

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" wrote
This is a portion of the table pasted I'm working on, I hope may be
lightful.

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