Forum Discussion
Create query displaying sum and count and totals
I have a timesheet where every entry has a Category, and total number of hours spend on that task. It looks like this...
How can i create a query/summary on another sheet in my excel document that creates a summary showing the total number of hours contributed to each Category, and the total cost for each category. It would be good for this query/summary to be dynamic so if i add a entry on the timesheet this summary reflects that, such as new category names.
Sample timesheet has been attached.
4 Replies
If you have Microsoft 365 or Office 2021, you can use dynamic array formulas:
One way to do this is a pivot table.
The downside here is when i add a new row entry on the timehseet, the summary does not update to include the new entry. This is the part im trying to really have automated as this initial page will grow and it will be annoying to have to go and update the summary page manually every time a new entry/row is added.
A pivot table doesn't update itself automatically. The dynamic array formulas in my second sample workbook do.
The attached sample workbook (now a .xlsm) shows how to use code to update the pivot table automatically when the Summary sheet is activated.
