Home |
Search |
Today's Posts |
#1
|
|||
|
|||
adding cells with text and numbers
I have a yearly vacation planner that tracks vacations (V), sick days (S),
emergency days (E). The planner has 31 columns labelled 1 to 31 for 31 days in the month and 12 rows for 12 months of the year (Jan to Dec). If a person takes a sick day I enter the following in the appropriated month/day cell.: 'S.5' where S signifies it is a sick day and .5 signifies the half the day was taken as sick day. I would like to add all the sick days taken, or vacation days or total the emergency days taken for the year. Is there any way where I could truncate the first letter for each occurence of the same type and then add the numbers after the letter for all occurences of the same type i.e.'S'. I know another way around it is to add another column to each day column to put in what type of day was taken and then use the conditional sum but I was hoping to keep the spreadsheet to a minimum number of columns. Can you help? |
#2
|
|||
|
|||
Assuming the planner table is in A1:AF13
and say, A15:A17 contains the letters: S, V, E Put in the formula bar for B15, and array-enter (i.e. press CTRL+SHIFT+ENTER): =SUM(IF(--(LEFT($B$2:$AF$13,1)=A15),--(SUBSTITUTE($B$2:$AF$13,A15,"")))) Copy B15 down to B17 B15:B17 should yield the desired summations from the table for S,V,E -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Tat" wrote in message ... I have a yearly vacation planner that tracks vacations (V), sick days (S), emergency days (E). The planner has 31 columns labelled 1 to 31 for 31 days in the month and 12 rows for 12 months of the year (Jan to Dec). If a person takes a sick day I enter the following in the appropriated month/day cell.: 'S.5' where S signifies it is a sick day and .5 signifies the half the day was taken as sick day. I would like to add all the sick days taken, or vacation days or total the emergency days taken for the year. Is there any way where I could truncate the first letter for each occurence of the same type and then add the numbers after the letter for all occurences of the same type i.e.'S'. I know another way around it is to add another column to each day column to put in what type of day was taken and then use the conditional sum but I was hoping to keep the spreadsheet to a minimum number of columns. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
Adding cells with numbers and text | Excel Worksheet Functions |