Need to give a letter a value and sum
"Héctor Miguel" wrote:
hi, !
... the formula did well, but when an bare W is insert in any other day, or to use the previous example
on D2 it doesn't add it to others bare W or W with numbers, like W3.5.
The formula must sum W with a hidden value of 7.5 and others W with numbers but at the same time
could be other letters on the same row and should not interfere with the sum of the W's....
1.....A.....B.....C.....D.....E.................J. ............k
2.....L......W....................W3.5..........10 .0......W
not sure i'm following you quite wright, but assuming you need to "sum" any W (alone) by 7.5
and add any other W (with numbers) only it's numbers -?- same range (columns A:E)
[J2] =countif(a2:e2,"w")*7.5+sumproduct(--(left(a2:e2)="w"),--mid(a2:e2&"00",2,5))
if you need the sum of any numbers (not only if begins w/ W)...
[J2] =countif(a2:e2,"w")*7.5+sumproduct(--mid(a2:e2&"00",2,5))
modify/adapt/... according your (real) needs
regards,
hector.
__ PP __
your post is not so clear, but i guess you could start with something like:
[J2] =if(sumproduct(--mid(a2:e2&"00",2,5)),sumproduct(--mid(a2:e2&"00",2,5)),7.5)
if any doubt (or further information)... would you please comment ?
__ OP __
I need to give at the end of a row, a total of a particular value, say 7.5
to various letter like W or L, S, O, etc ---some 10 letters-- or sum an alphanumerical one.
1.....A.....B.....C.....D.....E.................J
2.....L......W.................................... ...7.5
Column J should keep just those letters, eg. W, on row #2, and change it to 7.5.
But if column B (with the W letter) should have a number aside, eg.W3.5 or other
must be keep this value instead of 7.5.on J.
I've been looking for some answer in this forum but not a clue at present.
Any help, could be helpful.
Thank for your advice. The formula work well but when between A2:E2 are others letters with numbers, like L3.5 etc, results end up #VALUE!. Also when there is L or S it sum up this one. Is there any way to exclude this letters and others. I'll set up in other worksheet for others letters but individually on different cells. I'll appreciate point out anything I'm doing incorrect.
|