Timesheet formulas -SUMPRODUCT & SUMIFS help needed

Hello All,

I am working to develop a Timesheet in excel with a Data Validation approach (i have initial 3 tabs which contain the validation list sources). I need help on the 'Timesheet' tab and on the 'Summary' tab to pull data based on daily timesheet which would be filled by team members.

I am not so acquainted with the SUMPRODUCT or SUMIFS approach and hence request here to see if someone can help to actually build this automation.

If this is more of time consuming job, please excuse me. I may be asking too much.

I have attached a file which i am trying to build & showing my expectations.

Thanking you in advance.

NOTE: the file contains many columns & so request patience.

Mahesh

Dear Excel experts,

Can someone please guide on this request please?

Thanks,
Mahesh

Hi JeteMc,

Thanks for your reply. I have modified my file and attached the updated version now describing how i need the formulas to be set up in Q2:Q7 and also for cells S2:S7 and T2:T7.

I also added/changed few fields in the validation lists now to refine my approach better.

The tough portion would be the 'Summary' sheet; where i need the KPI dashboard fields as mentioned in that sheet.

Let me know whether my approach is correct. if not, you may suggest a better way to go about this.

Thanks again for your interest to get this working.

Mahesh

Hi JeteMc,

Many thanks for the development on the file. Your formulas work perfect. Just help me understand how have you applied the CF rule for Column S (your point 2 above).

Also, i have removed the Column for Hours Target as it was not needed as we can directly compute Resource Utilization Achieved as Y or N based on the value of Hours Actual (which if is >=95%, then a Y else N).

I would request you to help me with the Summary dashboard; as & when you get time.

Again Thanks for your time & efforts to help on this. Is there a way i can reduce the Timesheet columns with a different approach?

Mahesh

Hi JeteMc,

Thanks for the clarification on CF.

Regarding Summary Sheet - let me explain my refined approach as below (please ignore whatever i may have mentioned in the Summary sheet in the file earlier). Now this is the refined thought as under-

1. What i intend to show through a graph is a Weekly summary with selected columns like:
- Week no.
- Service Name(s)
- Planned Hours for that services
- Billable Hours in current Week for respective services
-Billable Hours in the previous Week for respective services

I need the above to check the progression of the work relative to current week.

2. Next, i need a Graph showing Week wise Resource Utilization (stacked bars showing Utilization %) measured for that week. I intend to carry this graph ahead with all previous weeks also in the same graph so that i can see the utilization over previous weeks

3. Further, I need a Graph showing Billable hours ( as stacked bars for the week) measured against Hours on left side Y axis. And on right side Y axis measure against Utilization. Then show a line graph to display Utilization % as data labels on top of the Bars (which show Billable hours as well).

4. Then, i need a Monthly Summary (for all weeks consolidated of that month) to show the following KPIs . This may or may not be a graph.
- On-Time Delivery (not sure how to show this as we are tracking it as Y or N)
- Right first time Delivery (here also we have RFT green & Not RFT Red - so, some way to display the consolidated results)
- Resource Utilization Target - here also we can show Target Vs Actual for the month

All the above could be in a tabular format.

5. Final now, a Pie chart showing how were the Billable hours expended during the month (i.e. where did the efforts go - on Execution, on Meetings, on Follow-up & so on....)

Hope i am able to explain myself here. Please let me know if you have any questions / You may suggest a better approach.

PS - please feel to mock-up the sheet as per your requirement.

Thanks a lot again....your help is much appreciated.

Mahesh

Attachment 659740

Hi JeteMc,

Thanks...Almost near to what i expect. I want to show you the attachment which may be helpful for you to visualize the requirement #1 which we are currently working.

Can we get the graph in this arrangement? Hope you want to study this & reply then.

Thanks,
Mahesh

Uploaded again...please check now

Hi JeteMc.

I tried to build a mock-up of the above type of graph. Basically, added one more tab named as "Graphs"; and then inserted a table. Further applied SUMIFS formula to pull the Hours from the "Timesheet" tab; however there is something wrong in my formula as the values are not correctly populating.

NOTE: i just added a Column for Quarter in Timesheet (Col E) and updated my Service Name validation list as per above sample graph.

Please also check your Pivotgraph as i changed the filename. i dont know how to correct this error as it might not get refreshed

Let me know your views.

I am not sure whether this approach is OK or should we go with something different.

Thanks,
Mahesh

Thanks. I feel that Pivot graph gives more flexibility & may be i will follow the same. I think we have completed requirement #1.

One query though- Can we provide a full column reference in SUMIFS formula above; meaning, instead of a range for a column; if the SUMIFS is applied to entire column; then we might not be required to change the formula everytime; the timesheet entries are made or progressed? Will this Work?( I think i had tried in my earlier file; however i saw that you changed this to a range).


Can you help me with #2 (Next, i need a Graph showing Week wise Resource Utilization (stacked bars showing Utilization %) measured for that week. I intend to carry this graph ahead with all previous weeks also in the same graph so that i can see the utilization over previous weeks).

I think this also could be done with a Pivot table. However, i need support here. We can add another sheet named Graphs-2; where in we can place this graph for above #2.

Thanks,
Mahesh

Many Thanks for you support...JeteMc...

Before i mark this thread SOLVED; can you help me understand, how & where have you placed the calculated field of % Utilization in the Pivot Table. I am able to see 2 entries of % Utilization when i click the Field List of Pivot table..

I am OK as long as this serves the purpose. What i understand is that the %utilization for the week would be the average of utilization of all days in that week.

Your support is much appreciated!

Thanks!!

Ok...got it... Thanks for your efforts & time.

Just one thing - I downloaded the latest file -Timesheet and Utilization Master_V1.0 (MaheshK5277) and saved it with a diff name. After this, when i tried to test the timesheet entries; the Pivot tables were not getting refreshed. It is giving me an error "Can't open PivotTable source file'. How to solve this error. Please help.

Thanks,
Mahesh

Also, i found that the Validation reference is vanished in this file. Do i need to provide the Data Validation reference again for Columns where we had Dropdown entries?

Let me know please.

Thanks/Mahesh

Thanks a lot JeteMc for your support & guidance all throughout this post. Marking this as SOLVED.