sorting and summing

[Tman]

Bank Date Time Spent (Dec)
bisys 1/4/05 0.033
ctzri 1/4/05 0.033
ctzri 1/4/05 0.083
ctzri 1/4/05 0.083
ctzri 1/4/05 0.100
ctzri 1/4/05 0.317
ctzri 1/5/05 0.033
ctzri 1/5/05 0.250
ctzri 1/5/05 0.267
ctzri 1/6/05 0.050
I am attempting to sort this data by bank and by date then sum the data. I will then export the data to another sheet and display the sum of minutes for each bank for each day. for example the sum for ctzri on 1/4/05 will be displayed on a seperate sheet as .616 minutes. how can I accomplish this?

 

[Aladin Akyurek]

Consider building a pivot table from your data.

 

[adulted]

Hi Tman

A pivot table is a good suggestion. I was working on this prior to that post, how about something like the following:

Book4
	A	B	C
1	bisysCriteria
2	Bank	Date	TimeSpent
3	bisys
4
5	ctzriCriteria
6	Bank	Date	TimeSpent
7	ctzri	01/04/2005
8
9	Database
10	Bank	Date	TimeSpent
11	bisys	01/04/2005	0.033
12	ctzri	01/04/2005	0.033
13	ctzri	01/04/2005	0.083
14	ctzri	01/04/2005	0.083
15	ctzri	01/04/2005	0.1
16	ctzri	01/04/2005	0.317
17	ctzri	01/05/2005	0.033
18	ctzri	01/05/2005	0.25
19	ctzri	01/05/2005	0.267
20	ctzri	01/06/2005	0.05
21
22
23	Statistics
24	bisys	0.033
25	ctzri	0.616
Sheet1

 

[Tman]

That was so easy. I apreciate you taking the time to reply

 

[adulted]

Hi Tman,

I'm glad it helped, I've always got amazing responces from this board.

In addition, I wasn't sure how familiar you are with excel. It would be wiser to add Named ranges to specific areas of your spreadsheet.

Select A2:C3 Click Insert>Name>Define and assign a name to that range Click Add, choose OK i.e: BisysCriteria

Select A6:C7 Click Insert>Name>Define and assign a name to that range Click Add, choose OK i.e: CtzriCriteria

Select A10:C20 Click Insert>Name>Define and assign a name to that range Click Add, choose OK i.e: Database

In doing so, you can use the named range name rather then selecting the cells needed.

the formula in B25 =DSUM(A10:C20,C10,A6:C7)
would now read =DSUM(Database,C10,BisysCriteria)

Just a thought.