Share this content
6

Excel: Fast way to summarise/total?

Excel has following columns : Date, Employee, Rates, Duration, Amount and row is date order

Didn't find your answer?

I would like to be able to summarise and total by date and/or by employee e.g. Time for employee A for each calendar year, totalled by year, Amount for employee A for each calendar yesr, etc.  Are there any quick ways? Many thanks.

Replies (6)

Please login or register to join the discussion.

avatar
By daniel_
01st Dec 2019 17:43

Sounds like a job for pivot tables.
If its not already in a table, convert it to one by selecting everything and hit Ctrl+T.
Then when the cursor is in the table, go to insert > pivot table.

Example might be Date in the columns (remove everything other than years), employee field in Rows, either time or amount fields in your Values.

Thanks (1)
avatar
By johnhemming
01st Dec 2019 21:10

Another option would be to have a column which works out the calendar year from the date and then using sort with sub-totals to give the sub-totals.

Thanks (1)
avatar
By paulwakefield1
02nd Dec 2019 09:34

Pivot tables are indeed the obvious answer but another approach would be having, say, the years in a row and the names of the employees in a column and then use SUMIFS (or SUMPRODUCT) to create a table of values which could also be totalled by year.

Set the source as a table and the results will self expand when new data is added. If you have the very latest version of Excel, the UNIQUE dynamic array function will automatically add new employees. Years will still need to be added manually.

Thanks (1)
avatar
By BryanS1958
02nd Dec 2019 09:56

Thanks, I'm afraid I'm a newbie to pivot tables, I've done the easy bit and have filters for date, employee, etc. but, for example, each date has a tick next to it and there are about 5000 rows. If I deselect all in the date filter then there seems to be no way to select a range eg calendar year and total by employee. On mine even though the rows are in date order on the pivot table date selection all the years are muddled up so I can't use something like CTRL or SHIFT date range.

Thanks (0)
Replying to BryanS1958:
avatar
By john hextall
13th Dec 2019 14:29

You need to add a column to your data which picks the year bit out of the date. If you use a simple formula to do this, you can get the tax year, for instance, or any other category you want. Then use this resulting field in the pivot table to summarise by.

Thanks (0)
avatar
By paulwakefield1
02nd Dec 2019 10:38

Do you really want to filter? It might be easier to have the years in columns and the employees in rows.

If you set it up like that, you may well find that Excel automatically summarises it by month and/or year. If not, right click in the dates in the pivot table and "Group" and choose the way to group the dates that you want.

Thanks (0)
Share this content

Related posts