summing cells with text and numbers
 

I am trying to put together an attendance tracker that sums cells by
the type of time off time off taken. Unfortunately since the cells
contain both text and numbers I haven't figured out how to set up the
formula - can anyone help?

For example - I took 8hrs of vacation on 1/1/06, 2 hours of vacation on
1/2/06, 3 hours of sick time on 1/3/06 and 4 hours of sick time on
1/4/06. I am using "V" to signify vacation & "S" to signify sick time
so the cells would be 8V, 2V, 3S & 4S. My excel sheet would show the
dates 1/1/06 to 1/4/06 in cells A1 to D1. My time off would show in
cells A2 to D2. Then I tried to use cell E2 to sum up the vacation time
& F2 to sum up the sick time. Basically I want it to tell me that I
have used 10 hours of vacation & 7 hours of sick time.

I tried to put an example below...

A B C D E F
1 1/1 1/2 1/3 1/4
2 8V 2V 3S 4S


Any help that someone could offer would be greatly appreciated!

summing cells with text and numbers
 

Why not be sensible and use one extra row and put the time off indicators
there instead, this will work though

=SUMPRODUCT(--(RIGHT(A2:D2,1)=E1),--(SUBSTITUTE("0"&SUBSTITUTE(UPPER(A2:D2
),"V",""),"S","")))

Put the time off indicator in E1

You can also add criteria for dates as well to this

=SUMPRODUCT(--(RIGHT(A2:D2,1)=E1),--(SUBSTITUTE("0"&SUBSTITUTE(UPPER(A2:D2
),"V",""),"S","")),--(A1:D1=--"2006-01-01"),--(A1:D1<=--"2006-01-03"))

will only sum between Jan 1 2006 and Jan 3 2006

It would be easier to use an extra row for the indicators, especially if you
have more indicators than these 2


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com




"val" wrote in message
ups.com...
I am trying to put together an attendance tracker that sums cells by
the type of time off time off taken. Unfortunately since the cells
contain both text and numbers I haven't figured out how to set up the
formula - can anyone help?

For example - I took 8hrs of vacation on 1/1/06, 2 hours of vacation on
1/2/06, 3 hours of sick time on 1/3/06 and 4 hours of sick time on
1/4/06. I am using "V" to signify vacation & "S" to signify sick time
so the cells would be 8V, 2V, 3S & 4S. My excel sheet would show the
dates 1/1/06 to 1/4/06 in cells A1 to D1. My time off would show in
cells A2 to D2. Then I tried to use cell E2 to sum up the vacation time
& F2 to sum up the sick time. Basically I want it to tell me that I
have used 10 hours of vacation & 7 hours of sick time.

I tried to put an example below...

A B C D E F
1 1/1 1/2 1/3 1/4
2 8V 2V 3S 4S


Any help that someone could offer would be greatly appreciated!