formula for Excel
 

We have to track our day in 15 minutes increments, some which are billiable
or non-billable. I have set up the fomula that calculates the 15 minute
increments for each billable / non-billable. There are 4 billable and 4
non-billable categories, numbered 1 through 8 and these numbers are placed in
1 column which corrresponds with each time. What I can't figure out is how
to "pull" the data from the cells in each of the categories to one cell
representing each categorie. Each day will be different with different times
for each category, so nothing remains static. Suggestions would be greatly
appreciated.

formula for Excel
 

=SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200)

where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the
amounts.

You can put the test values in cells and check against these, better in a
table.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
We have to track our day in 15 minutes increments, some which are
billiable
or non-billable. I have set up the fomula that calculates the 15 minute
increments for each billable / non-billable. There are 4 billable and 4
non-billable categories, numbered 1 through 8 and these numbers are placed
in
1 column which corrresponds with each time. What I can't figure out is
how
to "pull" the data from the cells in each of the categories to one cell
representing each categorie. Each day will be different with different
times
for each category, so nothing remains static. Suggestions would be
greatly
appreciated.

formula for Excel
 

Below is the table that I'm using. (hope it posts this legible) The formula I
need is that everything that is a 1 in Activity, will add the those 15/min
increments up in a separate cell; everthing that is a 2, etc. Again each
month will be different.

Date Activity Begin Time End Time "15/min. Increments"

1/1/08 1 7:00 15:00 32.00
1/1/08 2 15:00 17:00 8.00
1/1/08 1 17:00 17:30 2.00
1/1/08 8 17:30 17:55 1.67
0.00
0.00
0.00

Formula for this column

=(D2-C2+(D2<C2))*24/0.25

I couldn't make your formula work.



"Bob Phillips" wrote:

=SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200)

where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the
amounts.

You can put the test values in cells and check against these, better in a
table.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
We have to track our day in 15 minutes increments, some which are
billiable
or non-billable. I have set up the fomula that calculates the 15 minute
increments for each billable / non-billable. There are 4 billable and 4
non-billable categories, numbered 1 through 8 and these numbers are placed
in
1 column which corrresponds with each time. What I can't figure out is
how
to "pull" the data from the cells in each of the categories to one cell
representing each categorie. Each day will be different with different
times
for each category, so nothing remains static. Suggestions would be
greatly
appreciated.

formula for Excel
 

Linda,

Would you like to expand on how your spreadsheet is laid out. I am finding
it difficult to follow what it is that you are asking for.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Linda" wrote in message
...
We have to track our day in 15 minutes increments, some which are
billiable
or non-billable. I have set up the fomula that calculates the 15 minute
increments for each billable / non-billable. There are 4 billable and 4
non-billable categories, numbered 1 through 8 and these numbers are placed
in
1 column which corrresponds with each time. What I can't figure out is
how
to "pull" the data from the cells in each of the categories to one cell
representing each categorie. Each day will be different with different
times
for each category, so nothing remains static. Suggestions would be
greatly
appreciated.

formula for Excel
 

Linda,

If you put the activities in M1,N1,O1, etc., then in M2 add

=SUMPRODUCT(--($B$2:$B$5=M$1),($D$2:$D$5-$C$2:$C$5))*24*4

and copy across.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
Below is the table that I'm using. (hope it posts this legible) The
formula I
need is that everything that is a 1 in Activity, will add the those 15/min
increments up in a separate cell; everthing that is a 2, etc. Again each
month will be different.

Date Activity Begin Time End Time "15/min. Increments"

1/1/08 1 7:00 15:00 32.00
1/1/08 2 15:00 17:00 8.00
1/1/08 1 17:00 17:30 2.00
1/1/08 8 17:30 17:55 1.67
0.00
0.00
0.00

Formula for this column

=(D2-C2+(D2<C2))*24/0.25

I couldn't make your formula work.



"Bob Phillips" wrote:

=SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200)

where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the
amounts.

You can put the test values in cells and check against these, better in a
table.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Linda" wrote in message
...
We have to track our day in 15 minutes increments, some which are
billiable
or non-billable. I have set up the fomula that calculates the 15
minute
increments for each billable / non-billable. There are 4 billable and
4
non-billable categories, numbered 1 through 8 and these numbers are
placed
in
1 column which corrresponds with each time. What I can't figure out
is
how
to "pull" the data from the cells in each of the categories to one cell
representing each categorie. Each day will be different with different
times
for each category, so nothing remains static. Suggestions would be
greatly
appreciated.

formula for Excel
 

Your formula worked great. Exactly what I wanted it to do. Thank you so
very much.

"Bob Phillips" wrote:

Linda,

If you put the activities in M1,N1,O1, etc., then in M2 add

=SUMPRODUCT(--($B$2:$B$5=M$1),($D$2:$D$5-$C$2:$C$5))*24*4

and copy across.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
Below is the table that I'm using. (hope it posts this legible) The
formula I
need is that everything that is a 1 in Activity, will add the those 15/min
increments up in a separate cell; everthing that is a 2, etc. Again each
month will be different.

Date Activity Begin Time End Time "15/min. Increments"

1/1/08 1 7:00 15:00 32.00
1/1/08 2 15:00 17:00 8.00
1/1/08 1 17:00 17:30 2.00
1/1/08 8 17:30 17:55 1.67
0.00
0.00
0.00

Formula for this column

=(D2-C2+(D2<C2))*24/0.25

I couldn't make your formula work.



"Bob Phillips" wrote:

=SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200)

where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the
amounts.

You can put the test values in cells and check against these, better in a
table.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Linda" wrote in message
...
We have to track our day in 15 minutes increments, some which are
billiable
or non-billable. I have set up the fomula that calculates the 15
minute
increments for each billable / non-billable. There are 4 billable and
4
non-billable categories, numbered 1 through 8 and these numbers are
placed
in
1 column which corrresponds with each time. What I can't figure out
is
how
to "pull" the data from the cells in each of the categories to one cell
representing each categorie. Each day will be different with different
times
for each category, so nothing remains static. Suggestions would be
greatly
appreciated.

formula for Excel
 

I'm back. The formula worked great for the first 4 rows that you wrote the
formula for. But when I added new numbers (1,2, or 3 thru 8) to the activity
column and the times in the begin and end and tried to expand the array to
include the new numbers and times, I received "0:00 or ##. Any suggestions?

formula for Excel
 

Linda,

Use this

=SUMPRODUCT(--(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)=M$1),(OFFSET($D$2,0,0,COUNTA($A:$A)-1,1)-OFFSET($C$2,0,0,COUNTA($A:$A)-1,1)))*24*4

You can then add more rows without changing the formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
I'm back. The formula worked great for the first 4 rows that you wrote
the
formula for. But when I added new numbers (1,2, or 3 thru 8) to the
activity
column and the times in the begin and end and tried to expand the array to
include the new numbers and times, I received "0:00 or ##. Any
suggestions?

formula for Excel
 

I tried it but it still does not work correctly. Is there anyway I can send
u a copy of the worksheet? It really is a simple spreadsheet. Maybe I'm
asking for more than Excel can be perform.

"Bob Phillips" wrote:

Linda,

Use this

=SUMPRODUCT(--(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)=M$1),(OFFSET($D$2,0,0,COUNTA($A:$A)-1,1)-OFFSET($C$2,0,0,COUNTA($A:$A)-1,1)))*24*4

You can then add more rows without changing the formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
I'm back. The formula worked great for the first 4 rows that you wrote
the
formula for. But when I added new numbers (1,2, or 3 thru 8) to the
activity
column and the times in the begin and end and tried to expand the array to
include the new numbers and times, I received "0:00 or ##. Any
suggestions?

formula for Excel
 

Yes sure

send it to

bob dot phillips at tiscali dot co dot uk

do the obvious with it

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Linda" wrote in message
...
I tried it but it still does not work correctly. Is there anyway I can
send
u a copy of the worksheet? It really is a simple spreadsheet. Maybe I'm
asking for more than Excel can be perform.

"Bob Phillips" wrote:

Linda,

Use this

=SUMPRODUCT(--(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)=M$1),(OFFSET($D$2,0,0,COUNTA($A:$A)-1,1)-OFFSET($C$2,0,0,COUNTA($A:$A)-1,1)))*24*4

You can then add more rows without changing the formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Linda" wrote in message
...
I'm back. The formula worked great for the first 4 rows that you wrote
the
formula for. But when I added new numbers (1,2, or 3 thru 8) to the
activity
column and the times in the begin and end and tried to expand the array
to
include the new numbers and times, I received "0:00 or ##. Any
suggestions?

formula for Excel
 

Nothing received here yet!

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Bob Phillips" wrote in message
...
Yes sure

send it to

bob dot phillips at tiscali dot co dot uk

do the obvious with it

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Linda" wrote in message
...
I tried it but it still does not work correctly. Is there anyway I can
send
u a copy of the worksheet? It really is a simple spreadsheet. Maybe I'm
asking for more than Excel can be perform.

"Bob Phillips" wrote:

Linda,

Use this

=SUMPRODUCT(--(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)=M$1),(OFFSET($D$2,0,0,COUNTA($A:$A)-1,1)-OFFSET($C$2,0,0,COUNTA($A:$A)-1,1)))*24*4

You can then add more rows without changing the formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Linda" wrote in message
...
I'm back. The formula worked great for the first 4 rows that you
wrote
the
formula for. But when I added new numbers (1,2, or 3 thru 8) to the
activity
column and the times in the begin and end and tried to expand the
array to
include the new numbers and times, I received "0:00 or ##. Any
suggestions?