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?

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?