| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filter under a total
How do I filter the following data to give me the total for each client under
the cumulative total of 21:00 hours. A B D E 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..... Regards -- capt |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
Uf the formula in cell E1 and then copy down column E. Notice where the
dollar signs are located. =SUMIF(B$1:B1,B1,C$1:C1) "capt" wrote: How do I filter the following data to give me the total for each client under the cumulative total of 21:00 hours. A B D E 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..... Regards -- capt |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
sorry the table seemed to get distorted.
-- capt "Joel" wrote: Uf the formula in cell E1 and then copy down column E. Notice where the dollar signs are located. =SUMIF(B$1:B1,B1,C$1:C1) "capt" wrote: How do I filter the following data to give me the total for each client under the cumulative total of 21:00 hours. A B D E 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..... Regards -- capt |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
A B D E
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..... -- capt "Joel" wrote: Uf the formula in cell E1 and then copy down column E. Notice where the dollar signs are located. =SUMIF(B$1:B1,B1,C$1:C1) "capt" wrote: How do I filter the following data to give me the total for each client under the cumulative total of 21:00 hours. A B D E 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..... Regards -- capt |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
I lookslike you have a header row , so put this formula in cell E2 and then
copy down column E =SUMIF(B$2:B2,B2,D$2:D2) "capt" wrote: sorry the table seemed to get distorted. -- capt "Joel" wrote: Uf the formula in cell E1 and then copy down column E. Notice where the dollar signs are located. =SUMIF(B$1:B1,B1,C$1:C1) "capt" wrote: How do I filter the following data to give me the total for each client under the cumulative total of 21:00 hours. A B D E 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..... Regards -- capt |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
Select the Total_hrs data, including header
Data Filter AutoFilter Click dropdown arrow - Custom Total_hrs - is less than - 21:00 The result will look like this: Date Client Hours Total_hrs 5-Sep-07 A 5:20 5:20 7-Sep-07 B 5:15 10:35 8-Sep-07 A 10:05 20:40 Advanced Filter will give you more options. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
Thanks Herbert,
Is there a way to do this with formulas or even vb codes? The result is what im looking for but just need it to be automated. -- capt "Herbert Seidenberg" wrote: Select the Total_hrs data, including header Data Filter AutoFilter Click dropdown arrow - Custom Total_hrs - is less than - 21:00 The result will look like this: Date Client Hours Total_hrs 5-Sep-07 A 5:20 5:20 7-Sep-07 B 5:15 10:35 8-Sep-07 A 10:05 20:40 Advanced Filter will give you more options. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
There are many formula solutions,
here is my very complex solution: Name the columns as suggested in the last post. Tools Options General R1C1 reference style. (For location-independent formula appearance) Name the 7 x 4 data array MyArray. (Don't include header) Insert Name Define Names: RowR Refers to: =ROW(INDEX(C1,1,):INDEX(C1,ROWS(MyArray),)) Create another 7 x 4 array and name it MyArray2. Copy the formats and headers from MyArray to MyArray2. Add a cell named TimeL , format it and enter 21:00. Put a 7 x 1 helper column next to MyArray2 and name it RowTm The array formula for RowTm is: =SMALL(IF((Total_hrs<TimeL),(Total_hrs<TimeL) *RowR,ROWS(MyArray)+1),RowR) Select the first row of MyArray2 and enter this array formula: =IF(RowTm R<ROWS(MyArray)+1,INDEX(MyArray,RowTm R,),"") Copy it down to the end of MyArray2. Revert back to the A1 reference style. The result will look like this: Date Client Hours Total_hrs RowTm 5-Sep-07 A 5:20 5:20 1 7-Sep-07 B 5:15 10:35 2 8-Sep-07 A 10:05 20:40 3 VBA can do everything and anything. For code, put yourself into the good hands of Gord Dibben. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
Thanks Herbert,
I shall try that. Seems very complicated though!!! I shall let you know how I get on. -- capt "Herbert Seidenberg" wrote: There are many formula solutions, here is my very complex solution: Name the columns as suggested in the last post. Tools Options General R1C1 reference style. (For location-independent formula appearance) Name the 7 x 4 data array MyArray. (Don't include header) Insert Name Define Names: RowR Refers to: =ROW(INDEX(C1,1,):INDEX(C1,ROWS(MyArray),)) Create another 7 x 4 array and name it MyArray2. Copy the formats and headers from MyArray to MyArray2. Add a cell named TimeL , format it and enter 21:00. Put a 7 x 1 helper column next to MyArray2 and name it RowTm The array formula for RowTm is: =SMALL(IF((Total_hrs<TimeL),(Total_hrs<TimeL) *RowR,ROWS(MyArray)+1),RowR) Select the first row of MyArray2 and enter this array formula: =IF(RowTm R<ROWS(MyArray)+1,INDEX(MyArray,RowTm R,),"") Copy it down to the end of MyArray2. Revert back to the A1 reference style. The result will look like this: Date Client Hours Total_hrs RowTm 5-Sep-07 A 5:20 5:20 1 7-Sep-07 B 5:15 10:35 2 8-Sep-07 A 10:05 20:40 3 VBA can do everything and anything. For code, put yourself into the good hands of Gord Dibben. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Filter under a total
I will ask Gord as well.
-- capt "capt" wrote: Thanks Herbert, I shall try that. Seems very complicated though!!! I shall let you know how I get on. -- capt "Herbert Seidenberg" wrote: There are many formula solutions, here is my very complex solution: Name the columns as suggested in the last post. Tools Options General R1C1 reference style. (For location-independent formula appearance) Name the 7 x 4 data array MyArray. (Don't include header) Insert Name Define Names: RowR Refers to: =ROW(INDEX(C1,1,):INDEX(C1,ROWS(MyArray),)) Create another 7 x 4 array and name it MyArray2. Copy the formats and headers from MyArray to MyArray2. Add a cell named TimeL , format it and enter 21:00. Put a 7 x 1 helper column next to MyArray2 and name it RowTm The array formula for RowTm is: =SMALL(IF((Total_hrs<TimeL),(Total_hrs<TimeL) *RowR,ROWS(MyArray)+1),RowR) Select the first row of MyArray2 and enter this array formula: =IF(RowTm R<ROWS(MyArray)+1,INDEX(MyArray,RowTm R,),"") Copy it down to the end of MyArray2. Revert back to the A1 reference style. The result will look like this: Date Client Hours Total_hrs RowTm 5-Sep-07 A 5:20 5:20 1 7-Sep-07 B 5:15 10:35 2 8-Sep-07 A 10:05 20:40 3 VBA can do everything and anything. For code, put yourself into the good hands of Gord Dibben. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filter Sub-Total | Excel Discussion (Misc queries) | |||
| How do i total a specific sum, using the filter? | New Users to Excel | |||
| Total on filter | Setting up and Configuration of Excel | |||
| Data filter total | Excel Worksheet Functions | |||
| How do I obtain total for auto filter value selected ? | Excel Discussion (Misc queries) | |||
Linear Mode
