Formula needed - automatically calculate session time totals for each user

[optimusprym8]

Hi, new to the forum, hope you peeps can help me!?

I have a CSV file of user data relating to session times on a server. The columns are as follows: username, uniqueID, loginTime, logoutTime.

What I want to be able to do is calculate for each user:

a) their individual session length of time
b) their total sessions length of time

OK simple enough and I have done this manually thus far by sorting the data by username and then creating a new column for 'sessionTime' and another for 'totalSessionTime' with loginTime subtracted from logoutTime (with the IF to allow for 24Hr time calculations)

I have been able to automate it slightly with

=SUMIF(A1:A50000,"username",G1:G10000)

where "username" represents an actual username that exists.

What I really would like to be able to do is automate the process so that when the file is updated with the latest data, and therefore probably new usernames that were not present before, all individual usernames are calculated automatically without me having to manually set usernames in each cell formula...

Does this make sense?

[Mallycat]

I think this type of thing is best handled with a pivot table. Start off by opening your CSV. Then click inside the range.

Select Data\Pivot Table
Click next

Drag your user name into the "drop rows here" section
On the pivot table menu, select Pivot Table\formulas\calculated field
Name the field TotalTime
Set formula = endtime -starttime
Click OK

[optimusprym8]

ok thanks will give that a go but what I really want it to do is pull out the username from the revelant column and do it automatically for all usernames. Is that possible?