Filter under a total

[capt]

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

 

[Joel]

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]

sorry the table seemed to get distorted.

 

[capt]

A B D E

 

[Joel]

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$22)

 

[Herbert Seidenberg]

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.

 

[capt]

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.

 

[Herbert Seidenberg]

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.

 

[capt]

Thanks Herbert,
I shall try that. Seems very complicated though!!!
I shall let you know how I get on.

 

[capt]

I will ask Gord as well.
--
capt

Thanks Herbert,
I shall try that. Seems very complicated though!!!
I shall let you know how I get on.