software

We have a worksheet that we use for time management. It has the day's of the
week vertically, with 15 minute increments. To the left is a list from 1 to
10 on the possible job duties we would place in the time slots. I would like
to have a formula that would tell me how many each of a particular job duty I
would spend on during the day. Example.
1 E Mail
2 Projects
3 Quotes
4 research
And so on...

7:00 - 2
7:15 - 2
7:30 - 3
7:45 - 2
8:00 -2
8:15 - 1
8:30 - 1
8:45 - 2
And so on...

I would like to select the range and tell me how many times a particular job
appears in a day. thanks.

You can use the COUNTIF(Range, Criteria) function. The following example
would count the occurrences of Email in the range B1:B20

=COUNTIF(B1:B20,quot;=Emailquot;)--
Kevin Backmannquot;Mike Buschquot; wrote:

gt; We have a worksheet that we use for time management. It has the day's of the
gt; week vertically, with 15 minute increments. To the left is a list from 1 to
gt; 10 on the possible job duties we would place in the time slots. I would like
gt; to have a formula that would tell me how many each of a particular job duty I
gt; would spend on during the day. Example.
gt; 1 E Mail
gt; 2 Projects
gt; 3 Quotes
gt; 4 research
gt; And so on...
gt;
gt; 7:00 - 2
gt; 7:15 - 2
gt; 7:30 - 3
gt; 7:45 - 2
gt; 8:00 -2
gt; 8:15 - 1
gt; 8:30 - 1
gt; 8:45 - 2
gt; And so on...
gt;
gt; I would like to select the range and tell me how many times a particular job
gt; appears in a day. thanks.

You only need a single column, say column B:
2
2
3
2
2
1
1
2
then
=COUNTIF(B1:B8,quot;=2quot;) will yield 5 the number of increments for projects
(code#2)
use quot;=1quot;, etc. for the other codes.
--
Gary's Studentquot;Mike Buschquot; wrote:

gt; We have a worksheet that we use for time management. It has the day's of the
gt; week vertically, with 15 minute increments. To the left is a list from 1 to
gt; 10 on the possible job duties we would place in the time slots. I would like
gt; to have a formula that would tell me how many each of a particular job duty I
gt; would spend on during the day. Example.
gt; 1 E Mail
gt; 2 Projects
gt; 3 Quotes
gt; 4 research
gt; And so on...
gt;
gt; 7:00 - 2
gt; 7:15 - 2
gt; 7:30 - 3
gt; 7:45 - 2
gt; 8:00 -2
gt; 8:15 - 1
gt; 8:30 - 1
gt; 8:45 - 2
gt; And so on...
gt;
gt; I would like to select the range and tell me how many times a particular job
gt; appears in a day. thanks.

I would do it slightly differently, use the code name not value

=COUNTIF(B:B,INDEX(I:I,MATCH(quot;Projectsquot;,J:J,0)))

where I holds the job code, J the job names, and B the job code against
time.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;Gary''s Studentquot; gt; wrote in message
...
gt; You only need a single column, say column B:
gt; 2
gt; 2
gt; 3
gt; 2
gt; 2
gt; 1
gt; 1
gt; 2
gt; then
gt; =COUNTIF(B1:B8,quot;=2quot;) will yield 5 the number of increments for projects
gt; (code#2)
gt; use quot;=1quot;, etc. for the other codes.
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Mike Buschquot; wrote:
gt;
gt; gt; We have a worksheet that we use for time management. It has the day's of
the
gt; gt; week vertically, with 15 minute increments. To the left is a list from 1
to
gt; gt; 10 on the possible job duties we would place in the time slots. I would
like
gt; gt; to have a formula that would tell me how many each of a particular job
duty I
gt; gt; would spend on during the day. Example.
gt; gt; 1 E Mail
gt; gt; 2 Projects
gt; gt; 3 Quotes
gt; gt; 4 research
gt; gt; And so on...
gt; gt;
gt; gt; 7:00 - 2
gt; gt; 7:15 - 2
gt; gt; 7:30 - 3
gt; gt; 7:45 - 2
gt; gt; 8:00 -2
gt; gt; 8:15 - 1
gt; gt; 8:30 - 1
gt; gt; 8:45 - 2
gt; gt; And so on...
gt; gt;
gt; gt; I would like to select the range and tell me how many times a particular
job
gt; gt; appears in a day. thanks.
I think it depends on how your data is setup, try one of these:

scenerio 1: dates repeated down the column for every time increment and time
in the ajacent column
e.g
col(A)-Col(B)-Col(C)
31/1/06 - 7:00 - 2
31/1/06 - 7:15 - 1
...
...
1/2/06 - 7:00 - 5
1/2/06 - 7:15 - 2

=SUMPRODUCT(($A$1:$A$1000=TODAY())*($C$1:$C$1000=2 ))*TIME(0,15,0)
or
=SUMPRODUCT(($A$1:$A$1000=$D$1)*($C$1:$C$1000=D2)) *TIME(0,15,0)
where D1 is a date and D2 is a code to lookup.

scenerio 2:dates in column followed by time increments till the next day
e.g
Col(A)-Col(B)
31/1/06
7:00 - 2
7:15 - 1
...
...
1/2/06
7:00 - 5
7:15 - 4

=COUNTIF(INDIRECT(quot;Bquot;amp;ROW(INDEX(A:A,MATCH(TODAY(), A:A,0) 1))amp;quot;:Bquot;amp;ROW(INDEX(A:A,MATCH(TODAY(),A:A,0) 41))),1)*TIME(0,15,0)
where 41 is the number of time increments during a day and the last quot;1quot; in
the formula is a code to lookup...again you can use cells instead that hold
the date and code.

scenerio 3: dates in column (a single date entry for the entire day) with
time increments in the adjacent column
e.g
Col(A)-Col(B)-Col(C)
31/1/06 - 7:00 - 4
- 7:15 - 2
...
...
1/2/06 - 7:00 - 3
- 7:15 - 2

=COUNTIF(INDIRECT(quot;Cquot;amp;ROW(INDEX(A:A,MATCH(TODAY(), A:A,0)))amp;quot;:Cquot;amp;ROW(INDEX(A:A,MATCH(TODAY(),A:A,0) 4 1))),1)*TIME(0,15,0)
again I would use cell references, especially for the codes then you could
type the formula adjacent to the first code on your list and copy down with
cells formatted as quot;h:mmquot;.

Hope this helps!
Jean-Guyquot;Mike Buschquot; wrote:

gt; We have a worksheet that we use for time management. It has the day's of the
gt; week vertically, with 15 minute increments. To the left is a list from 1 to
gt; 10 on the possible job duties we would place in the time slots. I would like
gt; to have a formula that would tell me how many each of a particular job duty I
gt; would spend on during the day. Example.
gt; 1 E Mail
gt; 2 Projects
gt; 3 Quotes
gt; 4 research
gt; And so on...
gt;
gt; 7:00 - 2
gt; 7:15 - 2
gt; 7:30 - 3
gt; 7:45 - 2
gt; 8:00 -2
gt; 8:15 - 1
gt; 8:30 - 1
gt; 8:45 - 2
gt; And so on...
gt;
gt; I would like to select the range and tell me how many times a particular job
gt; appears in a day. thanks.