Filter or sumproduct (Question for Gord Dibben)

[capt]

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

 

[Bob Phillips]

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)

 

[Joel]

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]

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?

 

[vezerid]

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

HTH
Kostis Vezerides