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!

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" <[email protected]> wrote in message
news:[email protected]...
>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!
>