Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula to track time against projects within a date range. Sum Hours (Timesheet!G:G) based on "Activity" (Timesheet!C:C to match against Reporting!B14); within date range (Reporting!>B49 &
Solved by I. H. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 18/07/2018 - 06:30
Reporting
Excelchat Expert 18/07/2018 - 06:30
Hi
User 18/07/2018 - 06:30
Hi
Excelchat Expert 18/07/2018 - 06:31
Could you please elaborate on the problem
Excelchat Expert 18/07/2018 - 06:31
It would be really helpgul if i can get a look at the sheets
Excelchat Expert 18/07/2018 - 06:31
*helpful
User 18/07/2018 - 06:32
ok, I have just copied them into the spreadsheet here
User 18/07/2018 - 06:33
So I am looking to match data included on "timesheet" with the project and date range on "reporting"
Excelchat Expert 18/07/2018 - 06:33
thanks
Excelchat Expert 18/07/2018 - 06:33
let me have a look
User 18/07/2018 - 06:33
Thank you! :)
Excelchat Expert 18/07/2018 - 06:35
Please help me understand what exactly you need
User 18/07/2018 - 06:36
Well, I need to figure out what formula will help me to analyse the data entered into "timesheet"
User 18/07/2018 - 06:37
So I need to enter something starting at C14 that counts how many hours have been spent working on 1:1 Positive People within the date range 25-5-18 to 14-6-18
Excelchat Expert 18/07/2018 - 06:37
okay
Excelchat Expert 18/07/2018 - 06:38
Should this be a different table, where you have activity, start date , end date and time spent in hours
Excelchat Expert 18/07/2018 - 06:38
?
User 18/07/2018 - 06:40
You mean like the "timesheet" one? where they enter Date ; Project and Hours?
Excelchat Expert 18/07/2018 - 06:41
No, just aggregate of the table in timesheet
Excelchat Expert 18/07/2018 - 06:41
You can chnage the date range ad the formula should show time spent accordingly
User 18/07/2018 - 06:41
hmmm what might that look like? And I guess I would still need to same formula to transfer the data from "timesheet" to "reporting"?
Excelchat Expert 18/07/2018 - 06:43
I am getting confused here... my understanding is you want analyze the data in timesheet by looking at how many hours where spent working on a particular activity during a time frame
Excelchat Expert 18/07/2018 - 06:43
Is my understanding correct?
User 18/07/2018 - 06:43
yes that's right and I want that to be shown on the reporting page
Excelchat Expert 18/07/2018 - 06:44
So, from the timesheet sheet, you want to populate the table in reporting tab
Excelchat Expert 18/07/2018 - 06:44
correct?
User 18/07/2018 - 06:44
yes :) so the hours need adding up in relation to each project on the reporting page
Excelchat Expert 18/07/2018 - 06:45
Easier way would be to use pivot table where do don't have to create formula
Excelchat Expert 18/07/2018 - 06:45
It can look be formatted as in the reporting tab
Excelchat Expert 18/07/2018 - 06:45
Should I go ahead with this?
User 18/07/2018 - 06:46
yes please, Ive never used pivot tables
Excelchat Expert 18/07/2018 - 06:46
Give me few minutes while work on it
User 18/07/2018 - 06:46
ok
Excelchat Expert 18/07/2018 - 06:51
I have created a new sheet Reporting Pivot
[Uploaded an Excel file]
User 18/07/2018 - 06:52
Thank you! :)
Excelchat Expert 18/07/2018 - 06:52
I hope this solves your problem
Excelchat Expert 18/07/2018 - 06:52
The value in the pivot table is dependent on the data in timesheet tab
Excelchat Expert 18/07/2018 - 06:53
Once you update the timesheet data, the only other thing you would have to do is refresh pivot table
User 18/07/2018 - 06:53
ok brilliant so does that mean that as additional project are added this will automatically be reflected in the pivot table?
Excelchat Expert 18/07/2018 - 06:53
Yes, but you would have to refresh the pivot table
Excelchat Expert 18/07/2018 - 06:54
Let me show you how
Excelchat Expert 18/07/2018 - 06:54
Select a cell on the pivot table > Analyze Tab > Refresh > Refresh
Excelchat Expert 18/07/2018 - 06:55
You find analyze tab on the top of the excel window
Excelchat Expert 18/07/2018 - 06:55
Did you follow?
User 18/07/2018 - 06:55
Got it, that's fantastic!
User 18/07/2018 - 06:56
Thanks so much, I have been working on this for hours - you've been so helpful!
Excelchat Expert 18/07/2018 - 06:57
you are welcome
Excelchat Expert 18/07/2018 - 06:57
Please provide feedback
User 18/07/2018 - 06:57
Will do, thanks again! :)

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.