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
|