+ Reply to Thread
Results 1 to 8 of 8

Vertical total of Horizontal Bar Chart value or total number between lots of range dates

  1. #1
    Registered User
    Join Date
    08-26-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    9

    Unhappy Vertical total of Horizontal Bar Chart value or total number between lots of range dates

    Hi guys I am new to the forum,

    Not sure if the title makes any sense but here is what I am trying to do.

    I am basically generating a schedule to show how many workers I would need for all of on going projects at a given date(during 3~4 week duration). I have generated a horizontal bar chart with project start and end date and have number of workers labeled on the right side.

    Is there a way to show how many workers are needed on each given day on a chart? It would be like taking the sum of vertical values from a horizontal chart. I am having trouble because the projects have different range of dates therefore the number of workers needed will have to total between various start date >= <=end date.

    It doesn't necessarily have to be a bar chart, it could be a linear chart reflecting range and count. As long as I can see the count for each day for the next few weeks. I also tried to create a pivot using sumifs with date range but the number generated is not what I was looking for.

    Not sure if how I explained makes sense.

    Ex.

    8/25 would show FTE = 8
    8/26 - 8/27 FTE = 16
    8/28 -09/09 FTE = 21
    09/10 - 09/11 FTE = 16
    09/12 - 09/15 FTE = 13
    09/16 - 10/14 FTE = 8
    and so on....


    This is confusing to explain.

    Please refer to the orange bar chart

    Thanks for the help!!!
    Attached Files Attached Files
    Last edited by harim4; 08-26-2020 at 04:17 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,865

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    The chart will not do the subtotaling for you, but a chart can certainly be used to visualize subtotals that are calculated in the spreadsheet. It's really just a question of what you are trying to subtotal and what formula you want to use to perform that subtotal. Unfortunately, I am not understanding how you want to perform the subtotals (or I am understanding, but your example values are not taken from the sample sheet).

    Assuming I understand what I am seeing, a subtotal of workers needed for a specific calendar day could be calculated using a SUMIFS() function:
    1) Enter 44068 (or 8/25/2020) into Y6. Enter =Y6+1 into Y7 and copy down as far as needed.
    2) Into Z6, enter =SUMIFS($N$6:$N$23,$C$6:$C$23,"<="&Y6,$E$6:$E$23,">="&Y6). Copy/paste/fill down as far as needed.

    That should give you a total of the "workers" in column N for each calendar day (though, again, I note that this gives different answers in this spreadsheet than you've indicated in the OP). I can then create a new chart (line) that shows the total number of workers needed for each calendar day.

    That's my best guess. If that isn't correct or doesn't help you find a correct formula, then you will need to explain better what you need to do.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    08-26-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    9

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    Hi MrShorty,

    Thank you for the answer. I will try to clarify.

    The example values FTE are taken form N column Worker(FTE) then added up to total for each duration at a given date.

    I want a working chart/table showing sum of workers for the next 4 weeks. But because all the projects have different duration and start/end date, I need the total # of workers allocated for the duration of all the projects at any given date.

    If you are looking at the chart I want to create a chart/table or whatever to show total of orange value that "today" line falls under (so right now for "today" I would need a number 8[3+5]). so 8 8 8 until project c starts then 16[3+5+8] and so forth.

    Hope this makes more sense.

    Thank you again

  4. #4
    Registered User
    Join Date
    08-26-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    9

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    Last edited by harim4; 08-26-2020 at 04:11 PM.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,865

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    I think what is confusing me is the 16 starting on 8/28. Prior to that, I can see the 3+5=8. On 8/28, I see 3+5+3=11. I don't see where the 8 is coming from in the 3+5+8=16 calculation that you are doing.

    In any case, a SUMIFS() function seems like it should work. It is just a matter of figuring out exactly what values you are summing.

  6. #6
    Registered User
    Join Date
    08-26-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    9

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    okay, it seem that I didn't know how to upload files properly and the updated file wasn't loaded. Sorry about the confusion. I have reloaded the file for clarity.

    If I were to use the Sumifs, how can I find the sum of workers on everyday of the week lets say starting Aug 25 to Sept 14? I can't figure out how to input the start and end date of projects while taking the sum of workers on given day.

    Thank you

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,865

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    Did you try the SUMIFS() function I proposed in post #2? As near as I can tell, it looks like that should work.

  8. #8
    Registered User
    Join Date
    08-26-2020
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    9

    Re: Vertical total of Horizontal Bar Chart value or total number between lots of range dat

    I tried doing this but only got error.
    But I did exactly as you said then used it to chart and it worked!!!!!!
    Spent days trying to figure this out.




    Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!

+ 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. Stacked Bar / Gantt Chart Vertical Total
    By cmck_1997 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-26-2019, 12:20 PM
  2. Pivot pie chart work % of total of company and total profit
    By faodavid in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-30-2018, 11:51 AM
  3. How change vertical alignment of dates in horizontal axis of chart?
    By fidgetyFingers in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-09-2018, 03:14 AM
  4. Adding two total horizontal lines [to a chart]
    By Gophermn86 in forum Excel General
    Replies: 1
    Last Post: 05-07-2018, 10:18 PM
  5. Replies: 6
    Last Post: 06-24-2016, 12:40 PM
  6. Pivot Chart Running Total - Removing Current Year Future Total
    By car3489 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-27-2013, 03:35 AM
  7. Replies: 0
    Last Post: 02-07-2013, 08:42 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