Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter question? | Excel Discussion (Misc queries) | |||
Question for Gord D | Excel Discussion (Misc queries) | |||
Filter and SumProduct | Excel Worksheet Functions | |||
Filter question | Excel Worksheet Functions | |||
A question for Gord Dibben | Excel Discussion (Misc queries) |