function help for counting text as a value....

[Kris M]

I have a schedule spreadsheet. i have 7 names in column A starting at row 4

the assignments are in Column B through O representing 2 weeks. (yes i have
them labeled too in rows 2 and 3 for day of the week and the date)

i can't remember the formula for calculating the assignment as a value of 12.

here is an example of my spreadsheet.

Sun Mon Tues Wed Thurs Fri Sat Sun Mon Tues Wed Thurs Fri Sat
17-Jan 18-Jan 19-Jan 20-Jan 21-Jan 22-Jan 23-Jan 24-Jan 25-Jan 26-Jan 27-Jan 28-Jan 29-Jan 30-Jan
Theresa AJ D AJ D OFF AJ D AJ D AJ D AJ D OFF OFF AJ D AJ D AJ D AJ D AJ D
Stephanie
Kris OFF AJ N AJ N AJ N AJ N AJ N AJ N AJ N AJ N OFF OFF AJ N AJ N AJ N
Lawahid AJ T AJ T AJ T AJ T AJ T OFF OFF AJ T AJ T AJ T AJ T AJ T OFF OFF
Amanda VA D VA D AJ D OFF OFF VA D VA D VA D AJ D VA D VA D VA D VA D OFF
David AJ N VA N VA N VA N VA N OFF VA N VA N VA N AJ N VA N OFF VA N VA N
Mike VA N OFF VA D VA D VA D VA N OFF AJ D VA D VA N AJ N VA N OFF VA D


now without counting on fingers and toes i can see that Mike is working 11
shifts . What function can I use to show that he would be working 132 hours
in this 2 week period?

 

[David Biddulph]

If you are getting the 11 by counting the number of cells which have
something other than "OFF", and you then want to multiply the 11 by 12, then
perhaps
=(COUNTA(B9:AA9)-COUNTIF(B9:AA9,"OFF"))*12
Change the range references as appropriate.