Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tat
 
Posts: n/a
Default 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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to convert Excel imported numbers from text to numbers? Alden Excel Discussion (Misc queries) 9 April 1st 05 09:51 PM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM
Converting Numbers to Text properly Shirley Munro Excel Discussion (Misc queries) 1 February 16th 05 04:01 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM
Adding cells with numbers and text EddieZ Excel Worksheet Functions 4 November 9th 04 01:43 PM


All times are GMT +1. The time now is 03:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"