Prev: List Disctinct Strings from Range of Cells
Next: proper formula or function
From: Kris M on 12 Dec 2009 05:02
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?
From: "David Biddulph" groups [at] on 12 Dec 2009 06:51
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.
--
David Biddulph

"Kris M" <KrisM(a)discussions.microsoft.com> wrote in message
news:2BE6B51F-0B22-450C-B1D2-29611F411D0D(a)microsoft.com...
>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?


 | 
Pages: 1
Prev: List Disctinct Strings from Range of Cells
Next: proper formula or function