B

#### Babylynn

Alpha codes: L = late, A= Absent. I then count these text values by using

Sumproduct to evaluate a 12 month period of time, based on the hire date of

the employee, with a current column of dates. In the same column, I want to

place numeric values to add up attendance hours, using the same rolling 12

month period, forward of the anniversary date.

Dan Smith anniversarry date 1/1/2009

Today is 4/30/2009

Date

1/1/2009 A (this value represents "absent")

1/2/2009

1/3/2009

1/4/2009 8 (this value represents 8 hours vacation)

1/5/2009

1/6/2009

1/7/2009

1/8/2009 L (this value represents "late")

ON the spread sheet there is an upper section that calculates in table how

many hours of vacation are available based on tenure, in column Q. It ranges

for 40 years. So if for example the employee has 5 years service, it will

return a value of 80 hours, in Column Q row 32. Then to the right in column

R, I accumulate the actual hours used, then in another cell, I calculate the

balance. So if it sees a value in column Q, it will reference the list of

dates in Column O, and sum the numbers in Column R, for a balance of vacation

hours used. The >< formulas compare their anniversary date to the dates in

column O, and keep a running range based on the current date.

My problem is this. I want to use Text and numbers in the same column, and

when I use the formula below, I get #VALUE! If I keep the text and numbers

in 2 separate columns, it all works just fine.

Formula for obtaining the numeric vacation totals on running 12 month range:

=IF(Q32>0,SUMPRODUCT((O$137:O$9000>=O32)*(O$137:O$9000<O33)*R$137:R$9000),0)

Formula for obtaining the text count for lates ("L"), on running 12 month

range:

m6 = 1 year ago from today

=SUMPRODUCT((O$137:O$9002>$M$6)*(Q$137:Q$9002="L"))