| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to add (sum) a row cells with letter
"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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding Cells with a letter in one of them | Excel Discussion (Misc queries) | |||
| Summing all cells containing the letter K | Excel Worksheet Functions | |||
| Formula for cells that contain a specific letter | Excel Worksheet Functions | |||
| How can I add a two letter prefix to text in cells? | Excel Discussion (Misc queries) | |||
| count cells in a coloumn with letter x in it | Excel Worksheet Functions | |||
Threaded Mode
