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!