summing cells with text and numbers

[val]

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!

 

[Peo Sjoblom]

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

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

Put the time off indicator in E1

You can also add criteria for dates as well to this

=SUMPRODUCT(--(RIGHT(A22,1)=E1),--(SUBSTITUTE("0"&SUBSTITUTE(UPPER(A22
),"V",""),"S","")),--(A11>=--"2006-01-01"),--(A11<=--"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