Filter or sumproduct (Question for Gord Dibben)
 

Hi Gord I have been told your the man to ask!
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.

A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

There only are two clients (A and B). What happens on a monthly basis is the
information is collated and a total hours figure is produced. This is done by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):

Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)

Point to note: "21:00 hours is a total of both clients hours.

To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is the
remainder ie 70% and 75%. The percentage value is displayed on the following
cells:

Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12

Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12

My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.

I would greatly appreciate any help on this.

Thank you

--
capt

Filter or sumproduct (Question for Gord Dibben)
 

H10:
=SUMPRODUCT(--(Sheet1!$B$2:$B$20="A"),--(Sheet1!$D$2:$D$20<TIME(21,0,0)),Sheet1!$C$2:$C$20 )

H12: =SUMIF(Sheet1!$B$2:$B$20,"A",Sheet1!$C$2:$C$20)-H10

similalrly for L10, L12

J10: =H10/($H10+$L10)

and similalrly for J12, N10, N12

--
---
HTH

Bob


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



"capt" wrote in message
...
Hi Gord I have been told your the man to ask!
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.

A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

There only are two clients (A and B). What happens on a monthly basis is
the
information is collated and a total hours figure is produced. This is done
by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):

Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)

Point to note: "21:00 hours is a total of both clients hours.

To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is
the
remainder ie 70% and 75%. The percentage value is displayed on the
following
cells:

Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12

Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12

My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.

I would greatly appreciate any help on this.

Thank you

--
capt

Filter or sumproduct (Question for Gord Dibben)
 

Th eproblem simplifies if you create a new row and change the following line
from
9-Sep-07 B 0:35 21:15
to
9-Sep-07 B 0:20 21:00
9-Sep-07 B 0:15 21:15

You can then use a "=sumif" to get all your sums. The percentage is just
additional aritnmetic.

"capt" wrote:

Hi Gord I have been told your the man to ask!
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.

A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

There only are two clients (A and B). What happens on a monthly basis is the
information is collated and a total hours figure is produced. This is done by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):

Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)

Point to note: "21:00 hours is a total of both clients hours.

To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is the
remainder ie 70% and 75%. The percentage value is displayed on the following
cells:

Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12

Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12

My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.

I would greatly appreciate any help on this.

Thank you

--
capt

Filter or sumproduct (Question for Gord Dibben)
 

Thank Bob,
That seems to work just fine. Great stuff!
Just one more question.
If I wanted to do 21:20 instead of 21:00 how would I modify the formula?
--
capt


"Joel" wrote:

Th eproblem simplifies if you create a new row and change the following line
from
9-Sep-07 B 0:35 21:15
to
9-Sep-07 B 0:20 21:00
9-Sep-07 B 0:15 21:15

You can then use a "=sumif" to get all your sums. The percentage is just
additional aritnmetic.

"capt" wrote:

Hi Gord I have been told your the man to ask!
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.

A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

There only are two clients (A and B). What happens on a monthly basis is the
information is collated and a total hours figure is produced. This is done by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):

Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)

Point to note: "21:00 hours is a total of both clients hours.

To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is the
remainder ie 70% and 75%. The percentage value is displayed on the following
cells:

Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12

Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12

My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.

I would greatly appreciate any help on this.

Thank you

--
capt

Filter or sumproduct (Question for Gord Dibben)
 

Change TIME(21,0,0) to TIME(21,20,0)

HTH
Kostis Vezerides

On Dec 10, 4:26 pm, capt wrote:
Thank Bob,
That seems to work just fine. Great stuff!
Just one more question.
If I wanted to do 21:20 instead of 21:00 how would I modify the formula?
--
capt

"Joel" wrote:
Th eproblem simplifies if you create a new row and change the following line
from
9-Sep-07 B 0:35 21:15
to
9-Sep-07 B 0:20 21:00
9-Sep-07 B 0:15 21:15

You can then use a "=sumif" to get all your sums. The percentage is just
additional aritnmetic.

"capt" wrote:

Hi Gord I have been told your the man to ask!
This is difficult to explain but I shall try.
My workbook has two worksheets (Main and Summary), I enter details on the
sheet ("Main") similar to the example layed out below.

A B C D
Date Client Hours Total
hours(cumulative)
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

There only are two clients (A and B). What happens on a monthly basis is the
information is collated and a total hours figure is produced. This is done by
selecting a month on a dropdown list and the month total is displayed as
follows in sheet("summary"):

Client A:
cell H10 Under 21:00 hours = value (in this case 15:25)
cell H12 Over 21:00 hours = Value (in this case 0:30)
Client B:
cell L10 Under 21:00 hours = value (in this case 5:35)
cell L12 Over 21:00 hours = Value (in this case 7:55)

Point to note: "21:00 hours is a total of both clients hours.

To add to this I need to display percentage as well. Client A contributes
30% of the cost under 21:00 hours and 25% over 21:00 hours. Client B is the
remainder ie 70% and 75%. The percentage value is displayed on the following
cells:

Client A:
Under 21:00 hours: cell J10
Over 21:00 hours: cell J12

Client B:
Under 21;00 hours: cell N10
Over 21:00 hours: cell N12

My problem I dont know the best way to tackle this, Filter, sumproduct or
even something else.

I would greatly appreciate any help on this.

Thank you

--
capt