+ Reply to Thread
Results 1 to 24 of 24

Timesheet formulas -SUMPRODUCT & SUMIFS help needed

  1. #1
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Question 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
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    Dear Excel experts,

    Can someone please guide on this request please?

    Thanks,
    Mahesh

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    I suggest trying to do this in smaller steps. For example step one would be to tell us what values you expect to be displayed in cells Q2:Q7 on the timesheet and why. Once that is automated then we could move on to columns S:T.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    Column Q (a bit tricky):
    1. Populate Q2:Q9 using: =IF(OR(H2="",ISNUMBER(SEARCH("Deliverable",H2))),"","NA")
    2. Use the following custom data validation rule: =OR(AND(ISERROR(SEARCH("Deliverable",H2)),Q2="NA"),AND(ISNUMBER(SEARCH("Deliverable",H2)),OR(Q2="Y",Q2="N")))
    Note that this will require the user to type either "Y" or "N" however it will reject other entries and using this method will not allow the user to change an "NA". If on the other hand a regular data validation drop down is used the user could change an "NA" to either "Y" or "N".
    Column S:
    1. Populate S2:S9 using: =IF(R2="","",IF(R2="Positive","RTF","Not RTF"))
    2. Apply two conditional formatting rules:
    For Green: =S2="RTF"
    For Red: =S2="Not RTF"
    bother rules are applied to S2:S9
    Column T:
    1. Populate T2:T9 using: =IF(L2="","",IF(L2>=95%,"Y","N"))
    2. Conditional formatting rule applied to T2:T9 =T2="Y"
    I will look at the Summary sheet later if no one else has proposed a solution beforehand.
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    To see the rules select a cell in the range S2:S9, then select conditional formatting (home tab) and then select manage rules.
    The rule for red is: =S2="Not RTF"
    The rule for green is: =S2="RTF"
    Take a look on the Summary sheet and see if the first couple of items are as you envision.
    As to "I need a Weekly summary to appear here based on timesheet filled by project leads" it would seem that the most efficient way to obtain that would be to apply a filter to a copy of the data on the Timesheet and filter by week number.
    As to " I need a graph to show billable hours with servicewise distribution for every week", see if the pivot table and pivot chart displays the information necessary.
    I'll leave it here for the time being and continue once I know that we are on the right track with the first two summaries.
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    Not sure that I understand item #1, however I produced a pivot graph based on my interpretation of what is desired.
    Notice that there is a filter applied to weeks so that only the current, assumed to be week 3, and previous week are displayed.
    Notice that there is a filter applied to hours category so that only billable hours are displayed.
    If this meets the requirement for #1, let us know and we will attempt to proceed.
    Let us know if you have any questions.

  10. #10
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    Selectin in Attachment 659740 post #10 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
    Please utilize the instructions in the banner at the top of the page.
    Let us know if you have any questions.

  12. #12
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    Uploaded again...please check now
    Attached Images Attached Images

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    That is a nice graph, it doesn't appear to be a pivot graph. We may be able to provide better help if you could provide a sample of this type graph based on the data in the sample file. Also let us know what improvements you want to make.
    Let us know if you have any questions.

  14. #14
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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
    Attached Files Attached Files

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    It seems to me that the only difference is that on the Graph sheet the total hours were showing the total of all billable hours for all weeks. On the Summary sheet the pivot table was only showing the total of the billable hours for a particular week. In the attached copy of the file I used the following formula for total hours:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  16. #16
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    Can we provide a full column reference in SUMIFS formula
    I suggest what I consider to be a better practice which is to convert the data on the Timesheet into a table. Notice that when you go to cell U7 and press tab the table automatically adds a row. Go to the graphs sheet and look at the cells containing the SUMIFS formulas and they will show the range as being 2:8 (whereas they previously showed 2:7)
    I have put a graph on the Graph-2 sheet that reflects my understanding of utilization % per week. Note that this does not reference the % Utilization column on the Timesheet as that column calculates per week per service name. To get the percentage for the chart a calculated field has been placed in the pivot table.
    The calculated field is populated using: ='Hours Actual' /'Hours Planned'

    Note that I feel that this thread has become so long as to discourage some contributors (who are really very knowledgeable about presentation graphs) from participating. I suggest that once we complete the work on the graph-2 sheet this thread be marked as 'Solved' and a new thread opened to look into the next graph showing Billable hours and Utilization.
    Let us know if you have any questions.

  18. #18
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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!!

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    To find the calculated field go to the Pivot Table Tools tab > Analyze sub tab > Fields, items & sets > Calculated field
    Select the drop down in the Name window and select % Utilization (notice that it has an extra space to differentiate it from the table column).
    % Utilization takes the sum of the actual hours for a week (column D of the Timesheet) and divides by the sum of the planned hours for that week.
    Let us know if you have any questions.

  20. #20
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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

  21. #21
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    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

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    In the Timesheet and Utilization Master_V1.0 (MaheshK5277) the pivot table on the Graph-2 sheet uses Table1 as a source.
    Select the PivotTable Tools tab > Analyze subtab > Change data source > Change data source and make sure the name in the Table/Range window matches the name of the table on the Timesheet.

    It may be easier to provide the Data Validation reference again than to try and figure out what happened to them.

    Let us know if you have any questions.

  23. #23
    Registered User
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    2010
    Posts
    90

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

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

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,816

    Re: Timesheet formulas -SUMPRODUCT & SUMIFS help needed

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. help needed, sumifs or index match formulas?
    By juca73 in forum Excel General
    Replies: 2
    Last Post: 05-11-2019, 09:41 AM
  2. [SOLVED] I can't find the right mix of formulas, sumifs, match, sumproduct
    By hlz21 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-18-2016, 09:24 AM
  3. [SOLVED] Advice needed - SUMIFS/SUMPRODUCT Issues
    By mo4391 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-23-2015, 02:35 PM
  4. Replies: 2
    Last Post: 04-19-2015, 02:00 AM
  5. Help needed with changing SUMIFS to SUMPRODUCT
    By J.U. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2014, 04:01 AM
  6. Timesheet IF formula needed and VLOOKUP help
    By gordymoore5 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-24-2013, 01:48 PM
  7. Replies: 0
    Last Post: 08-26-2011, 12:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1