| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New Validation option to format 1st letter as Capital letter | Excel Discussion (Misc queries) | |||
| Default Capital letter for 1st letter of a word | Excel Discussion (Misc queries) | |||
| Give RELEVANT responses to questions. DO NOT give usless list | Excel Worksheet Functions | |||
| Are you able to give a letter a number value | Excel Discussion (Misc queries) | |||
| column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
Threaded Mode
